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:

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.")


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:



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:







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:


With the data in the screenshot below again returns



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

26 responses

  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. Pingback: Excel Roundup 20160208 « Contextures Blog

  5. TEXTJOIN also accepts an array for the DELIMETER parameter
    results in “Excel|is-great/greatest”

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

  7. Pingback: Das neue Excel 2016 | Teil 1 Allgemeine Funktionen | Linearis :: BI für die Fachabteilung

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

  9. Pingback: Finding All Selected Items In A Slicer In Excel 2016 Using TextJoin() – Chris Webb's BI Blog

  10. Pingback: Using the Excel TEXTJOIN function to Extract an Internal Numeric String From a String by David Hager | Excel For You

  11. Pingback: Excel Roundup 20160208 – Contextures Blog

  12. Pingback: vba - Convertir rango cadena separada por comas

  13. Pingback: vba - Convertir gamme de chaîne délimitée par des virgules

Leave a Reply to sam Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: