CONCAT or TEXTJOIN
In a previous post, we talked about how the CONCAT function joins values to create a combined text string. As a quick review, the CONCAT function is designed to replace the CONCATENATE function and supports range references as well as cell references. Well, as soon as we start playing around with CONCAT to join range references, we quickly realize how nice it would be to automatically insert some type of separator between the cell values. But, to do that with CONCAT means going back to using individual cell references. And, as you may have guessed, this is exactly where the new TEXTJOIN function can come in handy!
Note: CONCAT and TEXTJOIN are available in Excel O365 and 2019+
Before we get to the mechanics, let’s be clear about our objective. Let’s say we have exported some transactions from our accounting system, and it contains individual columns for Dept, Account, and Subaccount, like this:
We want to write a formula in the FullAcct column that combines the Dept, Account, and Subaccount values so that they are separated by a colon, like this: 100:1000:90.
Since CONCAT supports the use of range references, we try it with a formula like this:
But when we hit Enter, we quickly realize that there are no colon delimiters. The result looks like this:
And, this is exactly where TEXTJOIN can help. TEXTJOIN allows us to specify a delimiter (a character to insert between the values), plus, it allows us to ignore blank cells which is extremely handy!
The syntax for the TEXTJOIN function is:
=TEXTJOIN(delimiter, ignore_empty, text1, ...)
So, let’s try this out on our workbook. We write the following formula into G7:
=TEXTJOIN(":", FALSE, D7:F7)
We hit enter and yes … it worked!
We fill it down and … hmmm:
They all look good, except when there is no Subaccount such as TransID 1014. There is an extra trailing colon. Although we could get help to remove the colon from other Excel functions, there is no need because we can change the ignore_empty value from FALSE to TRUE. Here is the updated formula:
=TEXTJOIN(":", TRUE, D7:F7)
We fill the updated formula down, and bam:
Yes … now it ignores empty cells and thus prevents us from getting extra delimiters. Nice!
If you have any other TEXTJOIN tips, please share by posting a comment below.
Sample file: TEXTJOIN.xlsx
Excel is not what it used to be.
You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.
Want to learn Excel?
Access all Undergrad and Masters lessons with a Campus Pass or CPE Pass. Includes on-demand training plus live office hours.