Best Microsoft Excel Bloggers

Thursday, January 25, 2018

TextJoin - An Excel 2016 Text Function

If you need to join a lot of text together in a string, check out TEXTJOIN.
It is a text function that was introduced in Excel 2016 to join text in cells together.
Initially, you might think that the function is not worth investigating. After all, we already have the ampersand (& ) as well as the CONCATENATE function to join text together.
However, TEXTJOIN has a decided advantage if you need to join multiple cells together because it only requires you to specify the delimiter once.
The syntax is =TEXTJOIN(delimiter, ignore empty, text…)
textjoin function









Let me give you a simple example using a person’s first, middle and last name and their professional designation.
textjoin function


If we used CONCATENATE, the equation would be : =CONCATENATE(A2,” “,B2,” “,C2,” “,D2). With TEXTJOIN the equation is  =TEXTJOIN(” “,,A2:D2).
textjoin text function









and this result would display: 
Lisa Marie Brown CPA
Clearly, TEXTJOIN is a bit simpler and a bit more elegant if you have a lot of cells that you want to join together and you want to use the same delimiter throughout.
Different Delimiters
You can use different delimiters if you wish and it is still simpler than CONCATENATE as well.
In this example, I wanted spaces after each of the names but I wanted a comma before the professional designation so I used 
=TEXTJOIN(” “,,A2,B2,C2,”,”,D2) and Excel displayed  the following result.
Lisa Marie Brown , CPA
So, add TEXTJOIN to your arsenal of text functions if you have Excel 2016.

No comments:

Post a Comment


Ms. Excel- Resident Excel Geek