Using TEXTJOIN and CONCATENATE for combining Text Strings in Excel

Learn about the different techniques for joining Text Strings in Excel, including the excellent new TEXTJOIN function

When it comes to the basic task of joining strings of data together, CONCATENATE has been our friend for a long time – but there’s a new kid on the block hoping to shake things up a little – and I think he’s succeeded.

We’re going to take a look through the options, from the most basic, to the newest addition

CONCATENATE and ‘&’

CONCATENATE is the stalwart function of joining text strings. Very simple to use, you simply list the values you want to join together.

=CONCATENATE(A1,B1,C1,D1,E1)

Not very helpful in this case, but we can concatenate additional characters

=CONCATENATE(A1," ",B1," ",C1," ",D1, " ",E1)

And, if you have blank cells, it will ignore these too…

Instead of CONCATENATE, you can also just use the & sign to achieve the same result, and behaves in the same way as concatenate

=A1&B1&C1&D1&E1

=A1&","&B1&"," etc

Which you use is at your discretion – if I’m just joining 2 items, I tend to use &, but if I’m doing something more complex, CONCATENATE makes it clearer what you’re doing.

But what if you want to delimit your fields, and ignore blank spaces, it does get a little messier – we have to test each cell, and then concatenate the answers

=CONCATENATE(IF(LEN(TRIM(A1))=0,"",A1&","),IF(LEN(TRIM(B1))=0,"",B1&","),IF(LEN(TRIM(C1))=0,"",C1&","))

Not particularly clean – but we’ll come back to this problem shortly!

CONCAT

Introduced to Excel 2016 as part of the Feb 2016 update, CONCAT is the new replacement for CONCATENATE. It behaves in exactly the same way. I presume it was introduced to reduce the length of the function name. CONCATENATE continues to be supported, but it is advised to use CONCAT where practical.

=CONCAT(A1,B1,C1,D1,E1)

CONCAT is also the standard function used by Google Sheets

TEXTJOIN

CONCAT wasn’t the only change to text string joining we got in the Feb 2016 update. We’ve also been treated to a new feature-packed function to use.

Remember that formula we had to write, to join strings with a delimiter, ignoring spaces? Dislike having to select each cell individually? TEXTJOIN helps with both of these.

The syntax is as follows…

=TEXTJOIN([delimiter],[Ignore Blanks],[Range1],[Range2]…)

So lets break that down.

The Delimiter lets us specify what (if any) character we want to separate our text with. This might be a comma, a space, both, nothing, or anything you like

Ignore Blanks is a simple TRUE/FALSE (or 1/0) value on whether you want to include blank values

And Range1 lets us specify a range of cell values we want, rather than having to list each one. And if your cells aren’t in a neat continuous range, that’s fine too, just specify further ranges to include.

Also note-worthy, is you range can be both multiple columns and multiple rows at the same time.

Lets look at a one of our earlier exaples.

We can use TEXTJOIN to concatenate with a space between each, without having to list each cell

=TEXTJOIN(" ",FALSE,B1:E1)

And how about concatenating with a comma, where we want to exclude blank values?

=TEXTJOIN(",",TRUE,A1:E1)

A far, far more elegant solution

TEXTJOIN is bound to be a very popular choice for anyone working with large amounts of text data – and really simplifies the solution.

1 comment