Home > Formulas > Text > TEXTJOIN

Excel TEXTJOIN Function Examples

Join address or other text strings with Microsoft Excel TEXTJOIN function. Formula examples, practice file. Similar to Excel Concatenate function, but more options. Available in Excel 365

Video: TEXTJOIN Function

In Excel for Office 365, or Excel 2019 and later versions, you can use the TEXTJOIN function to combine text from multiple ranges, quickly and easily.

This short video shows a couple of TEXTJOIN examples, and there are written steps, and more examples, below the video. Click here to download the sample file that was used in this video.

Video Timeline

• 00:00 Introduction
• 00:25 Old Way to Combine Text
• 00:54 TEXTJOIN - List of Days
• 02:20 TEXTJOIN With Formatting
• 02:48 Named Ranges

NOTES:

• If you don't have TEXTJOIN, you can use the ConCat user defined function, to combine text quickly.
• There is also a new CONCAT function (Microsoft site), but it's not as efficient as TEXTJOIN.

TEXTJOIN Function Examples

The sections below have 5 examples of using the TEXTJOIN function

-- TEXTJOIN Arguments

-- Example 1 - Simple Join

-- Example 2 - Join with condition

-- Example 3 - Condition and formatting

-- Example 4 - Dynamic array functions

-- Example 5 - Items with Line Breaks

-- Example 6 - Remove Non-Numeric Characters

TEXTJOIN Arguments

The TEXTJOIN function has 3 required arguments in its syntax:

1. 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
2. ignore_empty - TRUE, to ignore blank cells, or FALSE, to include them
3. text1 - The text items to join. This can be a text string, or range of cells, or an array of text strings

Note: You can add more text strings, if needed

• text2, text3, etc.

Example 1 - Simple Join

This example uses a simple TEXTJOIN formula to join all the text in a range of cells - A2:A8.

• delimiter (separator) is a comma and space -- the first argument is ", "
• blank cells will be ignored -- the second argument is TRUE
• items in cells A2:A8 will be combined -- third argument is A2:A8

This formula is in cell D3, and it returns all the days in the list.

=TEXTJOIN(", ",TRUE,A2:A8)

NOTE

If the formula had FALSE as the setting for ignore_empty, the blank cell (A5) would be included in the result:

• Sun, Mon, Tue, , Thu, Fri, Sat

TEXTJOIN with Line Break

In this example, for Excel 365, the values from two cells are combined, with a line break separating the values, using the new TEXTJOIN function.

• In cell A4, there is an order number
• In cell B4, there is a customer name

In cell C4, a TEXTJOIN formula will combine those two values, with a line break between them

TEXTJOIN Formula

In cell C4, the following formula combines values from A4 and B4, with a line break between them

• =TEXTJOIN(CHAR(10),TRUE,A4:B4)

Note: The Wrap Text format was applied to the OrderCust formula results, in column C.

Example 2 - Join with Condition

This example uses IF with TEXTJOIN to join the text in a range of cells - A2:A8, if it meets a specific condition..

• The separator is a comma and space, and blank cells will be ignored.
• The IF function checks for an "x" in column B. If an "x" is not found, IF returns an empty string for that weekday, which is ignored by TEXTJOIN, when TRUE is the 2nd argument.

The following formula is in cell D3, and it returns all the days in the list, where there is an "x" in column B.

=TEXTJOIN(", ",TRUE,IF(B2:B8="x",A2:A8,""))

TEXTJOIN With Conditions Video

In this video, Sarah shows the steps for creating the TEXTJOIN formula with conditions. This example is in the TEXTJOIN Examples workbook, which you can get in the Downloads section, below.

Video Timeline:

• 00:00 Introduction
• 00:17 TEXTJOIN Function
• 00:27 Simple TEXTJOIN Formula
• 00:58 Start the Formula
• 01:30 Check for an "X"
• 01:45 Add a Condition
• 01:55 What to Show
• 02:15 Formula Result
• 02:22 Get the Workbook

Example 3 - Condition and Formatting

This example example is similar to Example 2, but there are dates in column A, instead of weekday names.

• In the formula, the TEXT function formats the dates as "ddd", to show short weekday names in the result.
• The delimiter is a line break, created by the CHAR function, with 10 as the code..

=TEXTJOIN(CHAR(10),TRUE, IF(B2:B8="x", TEXT(A2:A8,"ddd"),""))

Example 4 - Dynamic array functions

In this example, TEXTJOIN is combined with a few of Excel's new dynamic array functions -- FILTER, SORT and UNIQUE. Dynamic arrays are available in Microsoft 365 plans, Excel for the web, and Excel mobile apps.

The Sales sheet has 2 years of sales data, formatted as an Excel table. The columns are named ranges - YrCol, RegCol, CatCol, NameCol and QtyCol.

On the Targets sheet, there is another table, with a sales target number for each year and category.

In column E, a TEXTJOIN formula creates a list of sales reps who met the sales target, for that row's year and category.

Sales Reps Who Met Target

The TEXTJOIN function combines the results from a few of Excel's new dynamic array functions -- FILTER, SORT and UNIQUE. To show how those functions work, there's a demo on the Report sheet.

There are drop down lists at the top of the sheet, where you can select a year and category. A SUMIFS formula returns the sales target amount for the selected year and category.

Cell B4 is named YrSel, cell C4 is named CatSel, and cell D4 is named TgtSel.

This dynamic array formula is in cell C7, and the results spill down to the cells below, if necessary.

=IFERROR(UNIQUE(SORT(FILTER(NameCol, (YrCol=YrSel) *(CatCol=CatSel) *(QtyCol>=TgtSel)))), "--")

In this formula, the FILTER function returns the rep names from the Name column in the Sales table, if all 3 criteria are met:

1. The year is equal to the selected year (YrSel)
2. The category is equal to the selected category (CatSel)
3. The quantity is greater than or equal to the target amount (TgtSel)

Then, the SORT function puts those names in alphabetical order, and the UNIQUE function removes any duplicate names.

Finally, the IFERROR returns two dashes ("--"), if there is an error in returning the sales rep names. For example, no sales reps reached the target amount for the selected year and category.

Show All Names in One Cell

On the Targets sheet, a similar formulas is used to create a list of sales reps. This is the function in cell E4 on the Targets sheet:

=IFERROR(TEXTJOIN(", ", TRUE, UNIQUE(SORT(FILTER(NameCol, (YrCol=B4) *(CatCol=C4) *(QtyCol>=D4))))), "--")

The TEXTJOIN function combines all the names, separated by a comma and space character, so the results are shown in a single cell, instead of spilling down a column.

Example 5 - Items with Line Breaks

In this example, TEXTJOIN is combined with FILTER, and other functions, to create a list of order details, all in one cell, with line breaks.

NOTE: In the Download section, there is also a sample file with numbered items with line breaks

The order details are pulled from a named Excel table, Sales_Data.

TEXTJOIN/FILTER Formula

On the OrderInfo sheet, there is a drop down in cell B3, where you can select an Order number.

Here is the formula in cell B4, which lists all the items from the selected order. The formula is colour coded to show the different functions, and there are details below on how the formula works.

• =SUBSTITUTE(TEXTJOIN(", ",FALSE, FILTER(CHOOSE({1,2,3,4}, Sales_Data[Category], Sales_Data[Product], Sales_Data[Grams], Sales_Data[Quantity]&CHAR(10)), Sales_Data[Order]=B3)), CHAR(10) & ", ",CHAR(10))

A) FILTER and CHOOSE

In the formula, FILTER returns records where the order number matches the order number in cell B3.

• FILTER(CHOOSE({1,2,3,4}, Sales_Data[Category], Sales_Data[Product], Sales_Data[Grams], Sales_Data[Quantity]&CHAR(10)), Sales_Data[Order]=B3)

With the CHOOSE function, 4 columns are selected for the FILTER

• For the first argument, there's an array of 4 numbers -{1,2,3,4}
• Next, these 4 columns are selected: Category, Product, Grams, Quantity
• Quantity is combined with CHAR(10) -- a line break

B) TEXTJOIN

Next, TEXTJOIN combines the FILTER results, with a comma and space character as the delimiter. Ignore blanks is set to FALSE

• TEXTJOIN(", ",FALSE,FILTER(CHOOSE({1,2,3,4}, Sales_Data[Category], Sales_Data[Product], Sales_Data[Grams], Sales_Data[Quantity]&CHAR(10)), Sales_Data[Order]=B3))

C) SUBSTITUTE

Finally, SUBSTITUTE cleans up the TEXTJOIN result. To remove extra delimiters, it replaces any "line break comma space", with a line break

• =SUBSTITUTE(TEXTJOIN(", ",FALSE, FILTER(CHOOSE({1,2,3,4}, Sales_Data[Category], Sales_Data[Product], Sales_Data[Grams], Sales_Data[Quantity]&CHAR(10)), Sales_Data[Order]=B3)), CHAR(10) & ", ",CHAR(10))

Completed TEXTJOIN/FILTER Formula

Here is the completed formula in cell B4

=SUBSTITUTE(TEXTJOIN(", ",FALSE, FILTER(CHOOSE({1,2,3,4}, Sales_Data[Category], Sales_Data[Product], Sales_Data[Grams], Sales_Data[Quantity]&CHAR(10)), Sales_Data[Order]=B3)), CHAR(10) & ", ",CHAR(10))

The completed formula returns what we need in cell B4:

• List of all items in the selected order
• Item details in this order - category, product, size and quantity
• Details separated by comma space
• Line break after each item's details

Example 6 - Remove Non-Numeric Characters

In addition to combining text from multiple cells, the TEXTJOIN function can also combine specific characters from a text string in a single cell.

In this example, shown in the screen shot below, there are codes in column A, in cells A2:A6.

• Some cells have a mixture of numbers, text, and special characters, such as dollar signs.
• Other cells have numbers only

In column B, we need a formula that removes any non-numeric characters, and returns the numbers only, formatted as a real number (not a text number)

Here is the formula in cell B2, which was copied down to cell B6:

• =IFERROR(--TEXTJOIN("",TRUE, IFERROR(--MID(A2, SEQUENCE(LEN(A2)),1),"")),"")

How It Works

In the formula to remove non-numeric characters in a string, here's what each functions does:

• LEN: returns the number of characters in cell A2
• SEQUENCE: returns an array of numbers, from 1, to the number that LEN returns
• MID: returns each character in cell A2, using each position from the sequence of numbers
• 2 minus signs: The two minus signs before the MID function try to convert each MID character to a real number
• IFERROR: (before MID function) If the character can't be converted to a number, the error result is changed to an empty string
• TEXTJOIN: Combines all the numeric characters returned by MID. Empty strings are ignored
• 2 minus signs: The two minus signs before the TEXTJOIN function try to convert TEXTJOIN result to a real number
• IFERROR: (at start of formula) If the TEXTJOIN result can't be converted to a number, the formula result is an empty string
  • This will prevent error values if any code has only non-numeric characters

TEXTJOIN - Comma and Line Breaks

In this example, for Excel 365, TEXTJOIN and FILTER function are combined, in a complex formula.

The formula result has comma-separated item details, and a line break after each item.

• separates item details with commas
• adds line break after each item, to create a new line of text
• all items are listed within a single cell

Note: You can get this sample file (TEXTJOIN Line Breaks) in the Download section below

Sales Order Items

In the sample workbook, there is a named Excel table, Sales_Data, where all the order details are stored.

• There are multiple rows for each order
• Each item purchased in the order is on a separate row.

Order Summary Sheet

The sample workbook also has an Order Summary sheet, where you can:

• Select an Order ID in cell B3, from a data validation drop down list
• See all the items from the selected order ID, in cell B4

In cell B4, a complex formula combines the new TEXTJOIN and FILTER function with three of the older Excel functions - SUBSTITUTE, CHOOSE and CHAR.

TEXTJOIN and FILTER Formula

Here is the TEXTJOIN / FILTER formula in cell B4, which lists all the items from the selected order.

Note: The formula is colour coded to show the different functions.

• =SUBSTITUTE(TEXTJOIN(", ",FALSE, FILTER(CHOOSE({1,2,3,4}, Sales_Data[Category], Sales_Data[Product], Sales_Data[Grams], Sales_Data[Quantity]&CHAR(10)), Sales_Data[Order]=B3)), CHAR(10) & ", ",CHAR(10))

Here's how the commas and line breaks are added:

• Comma separators are in the TEXTJOIN function: TEXTJOIN(", "
• The line breaks are created with the &CHAR(10) code.
• At the end of the formula, SUBSTITUTE function removes the extra "comma space"
  • CHAR(10) & ", ",CHAR(10))

Get the Practice Files

TEXTJOIN Get Started: Click here to download the sample file for the Get Started with TEXTJOIN video. For Excel 2019 or 365. The zipped file is in xlsx format, and does not contain macros.

TEXTJOIN Examples: This workbook has TEXTJOIN examples 1, 2 and 3, which work in Excel for Microsoft 365, and in Excel 2019 or later versions. The zipped file is in xlsx format, and does not contain macros.

TEXTJOIN Dynamic: This workbook has the TEXTJOIN with Dynamic Arrays examples, which work in Excel for Microsoft 365. The zipped file is in xlsx format, and does not contain macros.

TEXTJOIN Line Breaks: This workbook has the TEXTJOIN with Line Breaks example, which works in Excel for Microsoft 365. The zipped file is in xlsx format, and does not contain macros.

TEXTJOIN Line Breaks - Numbered: This workbook has a TEXTJOIN with Numbered Line Breaks example, which works in Excel for Microsoft 365. The zipped file is in xlsx format, and does not contain macros.

TEXTJOIN Numbers Only: This workbook has the TEXTJOIN - remove non-numeric characters example, which works in Excel for Microsoft 365. The zipped file is in xlsx format, and does not contain macros.

More Text Functions

Combine Text/Numbers

Names, Split/Reverse First and Last

FORMULATEXT Function

CODE and CHAR functions

ADDRESS Function