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.

ABC
1Planet NameDistance from SUN (in AU)
* AU – Astronomical Unit
* 1 AU = 150,000,000 km
Order from SUN
2Mercury0.41
3Venus0.72
4Earth13
5Mars1.54
6Jupiter5.25
7Saturn9.56
8Uranus197
9Neptune308

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;

FormulaResultComments
=CONCAT(A2, B2, C2)Mercury0.41Observe that the result is not readable form. We need to add a separator between the values.
=CONCAT(A3, “-“,B3, “-“, C3)Venus-0.7-2This result is better than the above; more readable form, after adding a separator between the values.
=CONCAT(A4:C4)Earth13Ranges 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;

FormulaResultComment
=TEXTJOIN(“-“,  TRUE,  A7:C7)Saturn-9.5-6See, how simple it is to use this function over above functions. It is easy to manage this function.
=TEXTJOIN(“-“, , A8, B8, C8)Uranus-19-7Observe 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

Leave a Reply

Your email address will not be published.

Scroll to top