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 has entered the game recently and has 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 2016, Excel Online and Mobile, Excel for Android tablets and phones.
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:
It has 3 required and 1 optional arguments:
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.
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 has just two arguments one of which is optional:
There can be a maximum of 253 arguments for the text values.
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,)
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)
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)
=CONCAT(A2," ",B2," ",C2," ",D2," ",E2)
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)
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.
5 responses to "How to concatenate text in Excel using the TEXTJOIN and CONCAT functions"
Hi Maria,
Recently I had made an upgrade to my office suite from Office 2013 Professional Plus to Office 2016 Professional Plus.
As advertised I am unable to see the following functions
TEXTJOIN
CONCAT
SWITCH
MAXIFS
MINIFS
IFS
Please let me know whether anything needs to be configured to get this function available into Excel 2016.
Regards
Riv
Hi Riv,
Please look at the following article, it should help:
http://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_win10/excel-2016-pro-plus-i-am-missing-new-functions-ie/2ed8effa-48a6-4dc3-a5e6-2aea6d7918b6
HOW TO APPLY TEXTJOIN FORMULA IN EXCEL 2007,
I'm using MS Office Professional Plus 2016 and the TEXTJOIN function doesn't appear to exist. I couldn't find any add-in to use to activate it. How can I make this function available in my version of MS Excel?
Hello!
This feature is available on Windows or Mac if you have Office 2019, or if you have a Microsoft 365 subscription.