Excel TEXTJOIN Function

New in Excel 2016* is the TEXTJOIN Function. It's the grown-up sibling of CONCATENATE.

Both TEXTJOIN and CONCATENATE join text together, but TEXTJOIN enables you to more easily join a list or range of cells including a delimiter that you want to insert between each text value.

Best of all, TEXTJOIN allows you to ignore empty cells and you’ll see why this is important later on.

*Note: This function is available in Excel 2016 if you are an Office 365 subscriber and have the latest version of Office installed. It's also available with Excel Online, Excel Mobile, Excel for iOS, and Excel for Android phones and tablets. If you have Office 365 and can’t see the TEXTJOIN then it’s likely you’re on the Deferred Channel, which hasn’t it yet. You can check information about all channel releases for Office 365 client applications here. 

If you don’t have Excel 2016 keep reading because I’ll show you an alternative way to achieve the same results.

Download the Workbook

TEXTJOIN Function Syntax

First let’s start with the boring, but necessary stuff. The syntax:

TEXTJOIN(delimiter, ignore_empty, text1, [text2],…)

Delimiter – this is what you want each text string separated with. It can be another text string, a reference to a cell or an empty space, all surrounded by double quotes. Note: if a number is provided it will be treated as text.

Ignore_Empty – this is either TRUE (you want it to ignore empty cells in your range), or FALSE, or their numeric equivalents of 1 and 0.

Text1 – this is the text you want to join, it can be a text string, a range of cells, or an array of strings.

Text2... – You can continue adding text to join, up to a maximum of 252 including Text1. Note: the maximum length of the resultant text string is 32767 characters.

Excel TEXTJOIN Function Example

Let’s take a look at an example of the TEXTJOIN Function in action to create a dynamic text label.

In cell A3 above we’re using TEXTJOIN to return the list of items selected in the Slicer by referencing the row labels for the PivotTable in C8:C15. Note: I'm extending the range below C10 where the list currently ends to allow for growth in case all of the items in the Slicer are selected.

In this example I’m separating the text strings with a comma followed by a space, and I'm setting the Ignore_Empty argument to TRUE, as represented by 1 in the formula, so that any empty cells aren't separated by the comma as well.

You'll see later on in the method for earlier versions of Excel why this Ignore_Empty option is so much easier.

Tips:

1. Remove the Grand Total row from your PivotTable (if it’s not required) so you can extend the range of cells referenced in TEXTJOIN down below the PivotTable to allow for growth.
2. If you need the Grand Total then consider inserting a dummy PivotTable connected to the Slicer, purely for the purpose of building a dynamic label.
3. Add any text outside of TEXTJOIN that you don’t want separated by the delimiter. In this example =TEXTJOIN(", ",1,C8:C15)&" - Sales Results" I’ve used the ampersand symbol to add “ – Sales Results” as highlighted in red.

Alternatives to TEXTJOIN for Earlier Versions of Excel

If you don’t have Excel 2016 then you can achieve the same results with SUBSTITUTE and TRIM with a formula like so:

=SUBSTITUTE(TRIM(C8&" "&C9&" "&C10&" "&C11&" "&C12&" "&C13&" "&C14&" "&C15)," ",", ")&" - Sales Results"

The TRIM function allows you to include all of the cells in the PivotTable (i.e. C8:C15) that could contain a label. It then removes any empty cells and SUBSTITUTE replaces the spaces added between each remaining text string with a comma, or any delimiter you want.

Again, add any other text you want to concatenate outside of the SUBSTITUTE(TRIM… formula so that you don’t end up with delimiters in places they’re not wanted.

It’s a lot less elegant than the new TEXTJOIN function, but it does the job. Plus it’s unlikely you’re going to have a massive long list of text you want to join using this technique because it wouldn’t be much use (not that I can think of anyway).

Please Share

If you liked this please click the buttons below to share.