New Ways To Concatenate Text In Excel 2016 With CONCAT() And TEXTJOIN()

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:

https://support.office.com/en-us/article/What-s-new-in-Excel-2016-for-Windows-5fdb9208-ff33-45b6-9e08-1f5cdb3a6c73

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.

image

…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)

image

The TEXTJOIN() function is more flexible and I suspect will be very popular. It gives you two benefits over CONCAT():

  1. 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
  2. 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:

image

And

=TEXTJOIN(",",FALSE,"The","cat","sat","on","the","mat.")

Returns

The,cat,sat,on,the,mat.

image

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.

image

You can see an Excel Online worksheet with all these examples here.

22 thoughts on “New Ways To Concatenate Text In Excel 2016 With CONCAT() And TEXTJOIN()

  1. 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.

  2. 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

  3. I dunno, but the ampersand doesn’t seem too awfully difficult to me.
    (=”how ” & “difficult ” & “it ” & “is ” & “to ” & “concatenate …”)

  4. TEXTJOIN also accepts an array for the DELIMETER parameter
    =TEXTJOIN({“|”;”-“;”/”},1,{“Excel”;”is”;”great”;”greatest”})
    results in “Excel|is-great/greatest”

  5. 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!

  6. 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)),””))

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s