Written by Debra Dalgleish from Contextures Blog
One of the newest Excel functions is TEXTJOIN — use it to combine several text items, quickly and easily. It’s a big improvement over the old CONCATENATE function, and the ampersand (&) operator. There’s a simple example below, and a fancier one, which combines TEXTJOIN with a few other functions.
TEXTJOIN Arguments
You can use TEXTJOIN if you have Excel in Microsoft 365, or Excel 2019. Or, try it in the embedded Excel File, further down on this page.
The TEXTJOIN function has 3 required arguments:
- delimiter – A text string to separate the joined text items. Put the delimiter in double quotes, or refer to a cell that contains the delimiter
- ignore_empty – TRUE, to ignore blank cells, or FALSE, to include them
- text1 – The text items to join. This can be a text string, or range of cells, or an array of text strings
To get the sample files with these TEXTJOIN examples, and to see more examples, go to the Combine Text page on my Contextures site.
Video: Simple TEXTJOIN
This short video shows a couple of simple TEXTJOIN examples — how to combine weekday names, and how to combine formatted dates. There are written steps, and more examples, below the video.
Simple TEXTJOIN
In the screen shot below, there’s a simple TEXTJOIN formula in cell D3.
=TEXTJOIN(“, “,TRUE,A2:A8)
- delimiter – “, “ (command and space character)
- ignore_empty – TRUE — ignore blank cells
- text1 – A2:A8 — cells with the weekday names
The formula joins all the weekday names that are in cells A2:A8, and puts a comma and space character between them. Cell A5 is blank, so it’s ignored in the formula results.
NOTE: If the formula had FALSE as the setting for ignore_empty, the blank cell would be included in the result: Sun, Mon, Tue, , Thu, Fri, Sat
Fancy TEXTJOIN
Next, we’ll look at a much fancier formula! In this example, TEXTJOIN is combined with 3 of Excel’s new dynamic array functions — FILTER, SORT and UNIQUE.
The sample file has a sales data table, with 2 years of sales data. The columns are set up as named ranges:
YrCol, RegCol, CatCol, NameCol and QtyCol.
Target Table
On another sheet, we want to see who met the targets, for each year and product category. The fancy TEXTJOIN formula is in column E, Met Target, and the details are below.
NOTE: Dynamic arrays are available in Microsoft 365 plans, Excel for the web, and Excel mobile apps.
Formula Functions
To create the list of names in a single cell, the “Met Target” formula has 5 functions. Here’s the formula in cell E4, from the screen shot above:
=IFERROR(TEXTJOIN(“, “, TRUE, UNIQUE(SORT(FILTER(NameCol, (YrCol=B4) *(CatCol=C4) *(QtyCol>=D4))))), “–“)
Here’s what the 5 functions in that formula do:
- FILTER the rep names from the sales data, based on 3 criteria – Year, Category, Sales Target
- SORT the filtered rep names
- Return the UNIQUE rep names from the sorted list
- Combine the unique list of names (TEXTJOIN)
- Return 2 hyphens, if the result is an error (IFERROR)
FILTER Function
In that formula, the FILTER function does most of the work:
FILTER(NameCol, (YrCol=B4) *(CatCol=C4) *(QtyCol>=D4))
The FILTER function has 2 arguments:
- array – Return rep names from the Name column (NameCol)
- include – Return rows where all 3 criteria are met:
- The year is equal to the year in B4
- The category is equal to the category in C4
- The quantity is greater than or equal to the target amount in D4
TEXTJOIN
After the rep names are FILTERed, SORTed, and UNIQUE-ified, the TEXTJOIN function puts them all together.
- TEXTJOIN(“, “, TRUE, filtered list)
The first 2 arguments are the same as in the Simple Example, and the 3rd argument is the result of the UNIQUE, SORT and FILTER functions
- delimiter – “, “ (command and space character)
- ignore_empty – TRUE — ignore blank cells
- text1 – filtered list
Get the Sample Files
To get the sample files with these TEXTJOIN examples, and to see more examples, go to the Combine Text page on my Contextures site.
The sample files are in xlsx format, and do not contain any macros.
Try the TEXTJOIN Function
If you don’t have TEXTJOIN in your version of Excel, try it in this embedded workbook.
________________________
Excel TEXTJOIN Function Examples
________________________
Excel TEXTJOIN Function Examples is a post from Contextures Blog and is not allowed to be copied to other sites