I don’t normally blog about Excel topics outside of Power Query and Power Pivot, but I think anyone who has ever done any serious work in Excel has found how difficult it is to concatenate more than two text values (although there are plenty of blog posts like this one showing how to do it). Well, not any more – Microsoft have finally got around to addressing this problem with two new Excel functions, TEXTJOIN() and CONCAT(). The announcement, plus news of other new Excel functions and a new funnel chart type, is here:
First, the bad news: at the time of writing you’ll only be able to use these functions in Excel on the desktop if you’re using the click-to-run version of Excel 2016 that you get through an Office 365 subscription (ie the version installed through the Office 365 portal that gets updated by Microsoft automatically – probably not the same version that you’re running on your desktop if you work for a big company). It is available in Excel Online and Excel Mobile too. I guess they’ll appear in the regular, Windows-installer version of Excel 2016 in a service pack at some point in the future.
How about some examples? First of all, CONCAT() is a successor function for the old CONCATENATE() function – as far as I can see it does everything CONCATENATE() does but crucially also supports ranges as inputs as well as individual text values. So
=CONCAT("The ", "cat ", "sat ", "on ", "the ", "mat.")
Returns
The cat sat on the mat.
…but also, if you have each of your words in different cells (nb I’ve added spaces at the end of each word here), you can concatenate all the values in a range like this:
=CONCAT(B2:B7)
The TEXTJOIN() function is more flexible and I suspect will be very popular. It gives you two benefits over CONCAT():
- The ability to specify a delimiter – a character or characters (for example a comma or a space) to insert between each item of text you want to concatenate
- The ability to ignore empty values
Some examples…
First, using a space in the first parameter:
=TEXTJOIN(" ",FALSE,"The","cat","sat","on","the","mat.")
Returns once again
The cat sat on the mat.
…without having to add spaces to the end of each word, as I did with the CONCAT() example above:
And
=TEXTJOIN(",",FALSE,"The","cat","sat","on","the","mat.")
Returns
The,cat,sat,on,the,mat.
Finally, here’s an example of how TEXTJOIN() can be used with a range with the second parameter being set to TRUE to ignore empty cells in a range:
=TEXTJOIN(",", TRUE,B2:B9)
With the data in the screenshot below again returns
The,cat,sat,on,the,mat.
You can see an Excel Online worksheet with all these examples here.
If TEXTJOIN works with an array as the last argument, this would be a wonderful addition to Excel. If not, it would be just an interesting addition. Those who are hardcore Excel formula folk have been waiting for array to string for a long time.
Well, this works:
=TEXTJOIN(“,”, FALSE, {“The”,”cat”,”sat”,”on”,”the”,”mat.”})
but my desktop copy of Excel 2016 can’t see the January update yet and so I can only use the new functions in Excel Online, and as far as I can see you can’t create array formulas in Excel Online. Something to test properly in a few weeks.
Interesting. I am still on desktop Excel 2016, so have to wait until service pack to get this. Thanks for posting about these new (and certainly awesome) formulas
I dunno, but the ampersand doesn’t seem too awfully difficult to me.
(=”how ” & “difficult ” & “it ” & “is ” & “to ” & “concatenate …”)
Ah, but if you’re concatenating values in other cells the ability to reference a range is a massive help
How long before the rest of us on 365 get to see these things?
Any day, I guess…?
Actually, I just realised I needed to follow the instructions here: https://support.office.com/en-us/article/Install-the-First-Release-build-for-Office-365-for-business-customers-4dd8ba40-73c0-4468-b778-c7b744d03ead?ui=en-US&rs=en-US&ad=US to get the very latest First Release build on my desktop. Most Office 365 customers will need to wait for another few months, I think.
TEXTJOIN also accepts an array for the DELIMETER parameter
=TEXTJOIN({“|”;”-“;”/”},1,{“Excel”;”is”;”great”;”greatest”})
results in “Excel|is-great/greatest”
Cool, thanks – I didn’t think to check that!
The important delimiter for using an array formula as the last argument is the null string “”. So, if you have received your Excel 2016 update, test this array formula to see if the resultant string has only numbers in it (with no spaces). =TEXTJOIN(“”,TRUE,IF(ISNUMBER(B1:B17),B1:B17,””)) – assuming a mix of numbers and strings (and empty cells) in B1:B17. Thanks!
Just tested it on desktop Excel and it seems to work in exactly the way you want: I get a concatenated string with only numeric characters and no spaces.
That’s going to open up a lot of great formula constructions. Thanks!
So, w/o having access to the new functions, I believe that the following array formula will return a numeric string from a string (i.e. – 125 from AMDHF125KOI) by using this formula. =TEXTJOIN(“”,TRUE,IFERROR(VALUE(MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1)),””))
Of course, with the string in cell A1.
Yes, that formula does exactly what you thought it would – it returns 125.
Unfortunately, the German version does not work. It returns an empty cell, although the actual result (using F9 in the formula bar) is =”125″.
=TEXTVERKETTEN(“”;WAHR;WENNFEHLER(WERT(TEIL(A1;ZEILE(INDIREKT(“1:”&LÄNGE(A1)));1));””))
Use Crtl-Shift-Enter to enter the formula in the cell.
@David, thanks, somehow I ignored the array.
It wasn’t even late 🙁