Microsoft Excel – Text Functions – CONCAT, CONCATENATE and TEXTJOIN formulas

Through the series of Articles, we are discussing about different types of functions in Microsoft Excel. In this Article, we will discuss 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 form 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 beauty of this function it accepts range also as input. 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 advantage of using this function, over `CONCATENATE` function. Disadvantage is we can’t give the separator or delimiter value when we use the Range. Observe the result that, it is not in more readable form. We must have to use the separator; second formula is more useful in this situation.

`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 the multiple strings into a single string.

This function doesn’t allow to use Ranges as input values, whereas `CONCAT` function allows to use 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, because to provide backward compatibility. We recommend to use 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 little bit annoying. Alternate to this is `TEXTJOIN` function. This has more advanced features than above two functions.

`TEXTJOIN` function (introduced in Office 2016)

This function is used to join the multiple strings; like `CONCAT` function. The difference is, we can provide the delimiter to place, in between each string. And also we can ignore empty string (if any), to NOT to combine.

The Syntax of this function is:

`=TEXTJOIN(delimiter, ignore_empty, text1, [text2], .....)`

Where delimiter is the delimiter to add between the strings. Through 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. Below examples explain in better;

 Formula Result Comment =TEXTJOIN(“-“,  TRUE,  A7:C7) Saturn-9.5-6 See, how simple it is to use this function over 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 consider it as TRUE for ignore_empty. In this example, we passed individual cell references instead of a Range.

We will discuss more features & functions of Excel, in our upcoming Articles.

🙂 Sahida

Microsoft Excel – Text Functions – CONCAT, CONCATENATE and TEXTJOIN formulas
Scroll to top