Excel

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)

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:

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.

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

    1. Chris Webb – My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.
      Chris Webb says:

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

    1. Chris Webb – My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.
      Chris Webb says:

      Ah, but if you’re concatenating values in other cells the ability to reference a range is a massive help

  4. How long before the rest of us on 365 get to see these things?

    1. Chris Webb – My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.
      Chris Webb says:

      Any day, I guess…?

      1. Chris Webb – My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.
        Chris Webb says:

        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.

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

    1. Chris Webb – My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.
      Chris Webb says:

      Cool, thanks – I didn’t think to check that!

  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!

    1. Chris Webb – My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.
      Chris Webb says:

      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.

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

      1. Chris Webb – My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.
        Chris Webb says:

        Yes, that formula does exactly what you thought it would – it returns 125.

    1. 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));””))

Leave a ReplyCancel reply

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