How to Use the Excel TEXTJOIN Function to Combine Strings with Delimiters

Before Excel 2016, combining text from multiple cells was a tedious process. Users had to rely on the ampersand (&) operator or the CONCATENATE function, manually typing delimiters (like commas or spaces) between every single cell reference. This made combining large ranges of data virtually impossible without complex VBA scripts. The introduction of the TEXTJOIN function revolutionized string manipulation by allowing users to combine massive ranges of text with a specified delimiter in a single, simple formula.

Understanding the TEXTJOIN Syntax

The TEXTJOIN function requires three core arguments:

=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

• delimiter: The character(s) you want to insert between each piece of text (e.g., a comma, a space, or a hyphen). This must be enclosed in quotation marks.
• ignore_empty: A boolean value. If TRUE, Excel will ignore any blank cells in your range, preventing double delimiters (e.g., “Apple,,Banana”). If FALSE, it will include the empty spaces.
• text1, text2, etc.: The cells, strings, or ranges of cells you want to combine.

Example 1: Creating a Comma-Separated List

Imagine you have a list of employee names in column A, from A2 down to A50, and you need to paste them into an email “To” field as a single, comma-separated string.

Instead of typing =A2&", "&A3&", "&A4..., you can simply use:

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

This formula tells Excel to take every name from A2 to A50, separate them with a comma and a space, and ignore any blank cells in the list.

Example 2: Combining First, Middle, and Last Names

If you have First Name in B2, Middle Initial in C2, and Last Name in D2, you can combine them into a full name in E2.

=TEXTJOIN(" ", TRUE, B2:D2)

The true power of TEXTJOIN is the ignore_empty argument. If a person does not have a Middle Initial (cell C2 is blank), TEXTJOIN will simply combine the First and Last name with a single space. The old CONCATENATE method would have resulted in two awkward spaces between the names.

Example 3: Using a Line Break as a Delimiter

You can even use TEXTJOIN to format an address block into a single cell, placing each component on a new line. To do this, use the CHAR(10) function as the delimiter, which represents a line break character in Windows.

=TEXTJOIN(CHAR(10), TRUE, A2:D2)

Note: For the line breaks to display correctly, you must select the formula cell and ensure “Wrap Text” is enabled on the Home tab of the Excel ribbon.