Through the series of Articles, we are discussing different types of functions in Microsoft Excel. In this article, we will discuss a few more Text Functions in Microsoft Excel.
We are going to use below Table of values in this article, and we apply these formulas to combine the strings to see the results.
| ♦ | A | B | C |
| 1 | Planet Name | Distance from SUN (in AU) * AU – Astronomical Unit * 1 AU = 150,000,000 km | Order from SUN |
| 2 | Mercury | 0.4 | 1 |
| 3 | Venus | 0.7 | 2 |
| 4 | Earth | 1 | 3 |
| 5 | Mars | 1.5 | 4 |
| 6 | Jupiter | 5.2 | 5 |
| 7 | Saturn | 9.5 | 6 |
| 8 | Uranus | 19 | 7 |
| 9 | Neptune | 30 | 8 |
CONCAT function (introduced in Office 2016)
This function combines multiple strings and forms a single string. Note that, this function was introduced in Office 2016.
The Syntax of this function is:
=CONCAT(text1, [text2], ...)
It takes multiple strings as arguments. Where text1, text2 etc,. are the strings; or the cell references where the text is placed. One of the beauties of this function it accepts range also as input. The below example explains in detail;
| Formula | Result | Comments |
| =CONCAT(A2, B2, C2) | Mercury0.41 | Observe that the result is not readable form. We need to add a separator between the values. |
| =CONCAT(A3, “-“,B3, “-“, C3) | Venus-0.7-2 | This result is better than the above; more readable form, after adding a separator between the values. |
| =CONCAT(A4:C4) | Earth13 | Ranges are allowed in this function. This is one of the advantages of using this function, over CONCATENATE function. The disadvantage is we can’t give the separator or delimiter value when we use the Range. Observe the result that, it is not in a more readable form. We must have to use the separator; the second formula is more useful in this situation. |
CONCAT function is widely used when we want to merge different data from different cells and want to make it a single cell for any specific purpose.
CONCATENATE function
In earlier versions of Excel (before 2016), we have this function to combine strings. This is replaced with CONCAT function in Office 2016. CONCAT and CONCATENATE are both are same and are used to combine multiple strings into a single string.
This function doesn’t allow to use of Ranges as input values, whereas CONCAT function allows using the Ranges. If we attempt to use the Ranges;
=CONCATENATE(A6:C6)
it will throw, #VALUE! Error.
Important: This function is obsolete in new versions of Excel, from Excel 2016. Instead, we can use CONCAT or TEXTJOIN functions. We are seeing this still to provide backward compatibility. We recommend using newer functions, instead of this version of join functions.
Both these functions do not add any delimiters, by default, when combining the strings. If we want to use the delimiter, we have to explicitly specify them wherever required; and if we want to use the delimiter in multiple places, we need to specify it in each and every place. This is a little bit annoying. Alternate to this is TEXTJOIN function. This has more advanced features than the above two functions.
TEXTJOIN function (introduced in Office 2016)
This function is used to join multiple strings; CONCAT function. The difference is, we can provide the delimiter to place, in between each string. And also we can ignore empty strings (if any), to NOT combine.
The Syntax of this function is:
=TEXTJOIN(delimiter, ignore_empty, text1, [text2], .....)
Where delimiter is the delimiter to add between the strings. Through the ignore_empty argument, we can tell whether to ignore an empty string or not. With this, we can avoid any unnecessary spaces or delimiters added to the result. Through text1, text2, etc,. we can pass the strings to combine to produce a single string. This function allows Ranges also as input. The below examples explain better;
| Formula | Result | Comment |
| =TEXTJOIN(“-“, TRUE, A7:C7) | Saturn-9.5-6 | See, how simple it is to use this function over the above functions. It is easy to manage this function. |
| =TEXTJOIN(“-“, , A8, B8, C8) | Uranus-19-7 | Observe that, in the second argument, if we do not pass any value, it considers it as TRUE for ignore_empty. In this example, we passed individual cell references instead of a Range. |
TEXTJOIN function is widely used when we want to join multiple text values from different cells and we want to make it a single cell with a specific delimiter and also have the ability to ignore blank cells.
We will discuss more features & functions of Excel, in our upcoming Articles.
🙂 Sahida