New in Excel 2019* 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 2019 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 2019 keep reading because I’ll show you an alternative way to achieve the same results.
Download the Workbook
Enter your email address below to download the sample 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:
- 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.
- 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.
- 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 2019 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).
Mahia-John Mahiaini
Hi Mynda,
Is is possible to do textjoin in power query. If not what is the other alternative?
Mynda Treacy
Hi Mahia-John,
You can use ‘Merge Columns’ to join text in Power Query. This only works with data in columns, not rows. If you have a specific example please post it in our Excel forum.
Mynda
Yev
Hi Mynda,
Thanks for this.
My Excel 2016 (desktop version) doesn’t contain this new function. I think it is only available in Office 365.
As for grand total, can’t we do something like this: =SUBSTITUTE(your_TEXTJOIN_formula_here,”, Total:”,””)?
Mynda Treacy
Yes, it is only available to Office 365 Excel 2016 users.
I’m not sure what you’re referring to with your ‘grand total’ suggestion.
Mynda
Yev
I’m referring to your tips on what to do if you need Grand Total in your pivot table.
Instead of creating a “dummy” pivot table (Tip #2) you could simply use the Substitute function.
Mynda Treacy
Hi Yev,
Thanks for clarifying. My reason fo removing the Grand Total row from the PivotTable was so that the text ‘Grand Total’ wasn’t included in the text string resulting from the TEXTJOIN formula. I recommend the range referenced by TEXTJOIN extends past the end of the PivotTable thus allowing for growth. If you have the Grand Total row at the bottom of your PivotTable then that will be included in the text string, which you don’t want.
However, if you need the PivotTable for other uses, and require the Grand Total, then set up a dummy PivotTable purely for the purpose of the TEXTJOIN formula. This PivotTable doesn’t even need any values.
I hope I’ve understood your comment and addressed it correctly, but if not, please let me know.
Mynda
Yev
Hi Mynda,
Yes, I understand the reason behind tip#2. I should have explained myself more clearly.
I was offering an alternative way of getting rid of “Grand Total:” from the resulting TEXTJOIN list.
=SUBSTITUTE(your_TEXTJOIN_formula_here,”, Grand Total:”,””)
I can’t test it, because I don’t have Office 365, but I think it should work fine, since the result of TEXTJOIN is a string.
Apologies for the confusion.
Yev
Mynda Treacy
Hi Yev,
I see what you mean now. I think your first comment threw me because you only mentioned ‘Total’ so I didn’t follow what you meant.
You can change the formula to this:
Mynda
Chuck Trese
I don’t have 2016 yet, but thanks for the TEXTJOIN tip.
I have two use cases for “massive long lists”:
1) create an email distribution list. All (>100) names pulled out of a range of cells, and separated by a semicolon.
2) create a list of (>100) projects to be entered into an SQL query, where the query phrase looks like ……. WHERE table.project IN (‘projecta’, ‘projectb’, ‘projectc’, ……….. ‘projectz’).
Currently I use Excel 2013 and do both of these in a macro because CONCATENATE just isn’t up to the task. Would be nice to be able to just use a TEXTJOIN instead.
Mynda Treacy
Thanks for sharing the ideas, Chuck.
TEXTJOIN still has a 252 limit so it might not be any good to you either.
Mynda