May
24

How to concatenate text in Excel using the TEXTJOIN and CONCAT functions

by | updated on

Learn about two Excel techniques to concatenate strings using the brand new TEXTJOIN and CONCAT functions.

For a long time CONCATENATE has been the first function we thought of when we needed to combine text in Excel. A couple of new players have entered the game recently and have shaken the things up a bit. Yes, I'm talking about TEXTJOIN and CONCAT designed to help you combine a range of strings. Use these simple functions whenever you need to merge parts of names, addresses, or phrases, combine numbers and words. Currently, they are available in Excel for Microsoft 365 and Excel 2019 (both for Windows and for Mac) as well as in Excel Online.

The TEXTJOIN function - description and syntax

The TEXTJOIN function in Excel concatenates text from multiple ranges or strings. You can specify a delimiter to include between each text value and ignore empty cells. Even if the delimiter is an empty string, TEXTJOIN will successfully join text in Excel.

Here's how the function looks like:

TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)

It has 3 required and 1 optional arguments:

  • The delimiter lets you specify any character for separating your text. This may be a comma, space, ampersand, or anything you like. The characters must be enclosed by double quotes, or a reference to a valid text string. Any numbers will be regarded as text.
  • Ignore_empty is a required argument. If it's TRUE, empty cells will be ignored. You can include blank cells by setting this value to FALSE.
  • text1 lets you specify a range of cell values to concatenate rather than having to enter each one.
  • [text2, …] is an optional argument that allows specifying further ranges to include in if your range is not continuous.

There can be a maximum of 252 arguments for the text items. If the resulting string exceeds the cell limit of 32767 characters, the TEXTJOIN function returns the #VALUE! error.

The CONCAT function in Excel - description and syntax

Introduced as part of the update in February 2016, CONCAT replaces the CONCATENATE function in Excel. It works exactly the same way. Looks like it has been added to reduce the length of the function name. Also, CONCAT is the standard function used by Google Sheets. CONCATENATE continues to be supported to provide compatibility with the previous versions of Excel.

The structure of the function is as follows:

CONCAT(text1, [text2],…)

CONCAT has just two arguments one of which is optional:

  • text1 is the text entry to be joined. It can be a string, or a range of strings.
  • [text2, …] stands for additional text items to be combined.

There can be a maximum of 253 arguments for the text values.

Excel - concatenate strings using the TEXTJOIN and CONCAT functions

Joining text is one of the most common tasks in Excel. In the previous versions if you needed to concatenate text from several cells, it was necessary to specify each one individually. With the new TEXTJOIN and CONCAT functions, you can simply refer to a range and combine text strings from cell ranges with or without a delimiter.

The main limitation of the CONCATENATE() function that CONCAT() has addressed is that we can now select a range of cells to join while previously were limited to listing cells separately. Say, you want to combine parts of telephone numbers. With the Concatenate function, you will need to enter the cell addresses one by one.

=CONCATENATE(A1,B1,C1,D1,E1,)
Combine strings using the CONCATENATE function]

In this case, CONCAT looks much more compact. All you need to do is just pick the range with the Excel strings to concatenate.

=CONCAT(A1:E1)
Join numbers with the help of CONCAT]

At the same time, both CONCAT and CONCATENATE look the same when you need to join text using delimiters.

=CONCATENATE(A2," ",B2," ",C2," ",D2," ",E2)
How to use the Concatenate function in Excel]

=CONCAT(A2," ",B2," ",C2," ",D2," ",E2)
Use the CONCAT function to combine text in Excel]

As it can be easily seen, the functions don't process empty cells which results in extra spaces.

In this case, TEXTJOIN is destined to be a really popular choice for users working with large amounts of text data. You simply specify the space delimiter, choose to ignore empty cells and define the range. This is by far a smarter solution.

=TEXTJOIN(" ",TRUE,A2:E2)
Use the TEXTJOIN function to concatenate text in Excel]

If you have Excel 2016, do explore the TEXTJOIN and CONCAT functions to see how helpful they are and say bye-bye to concatenated and nested if statements when combining strings.

You may also be interested in