TEXTJOIN function

The TEXTJOIN function in Excel is used to combine or join multiple text values into one single text string, with a specified delimiter between them. This function is especially useful when you want to concatenate several values or text items, such as combining words, names, or cell values, and you want to insert a separator (like a comma, space, or any custom delimiter) between them.

Syntax:

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

Parameters:

• delimiter (required): The text or character that you want to place between each of the text items you’re joining (e.g., comma ", ", space " ", etc.).
• ignore_empty (required): A logical value (TRUE or FALSE) that specifies whether to ignore empty cells or values. If TRUE, empty cells or values will be skipped; if FALSE, empty cells will be included in the result.
• text1, text2, … (required): The text items or values that you want to join. You can provide individual text strings, cell references, or ranges of cells.

Key Points:

• The TEXTJOIN function can handle a large number of text items and supports both text strings and cell references.
• It allows you to specify whether to include empty cells in the joined result.
• The delimiter can be any string, including a space, comma, period, or custom character.

Examples of Usage:

1. Basic Concatenation with a Space Separator

If cell A1 contains "apple", and cell B1 contains "banana", and you want to join them with a space in between:

=TEXTJOIN(" ", TRUE, A1, B1)

This will return:

apple banana

Explanation:

• The function combines the values in A1 and B1 with a space (" ") between them.

2. Concatenating Multiple Values with a Comma

If cells C1, D1, and E1 contain "John", "Paul", and "George", and you want to join them with a comma and space:

=TEXTJOIN(", ", TRUE, C1, D1, E1)

This will return:

John, Paul, George

Explanation:

• The values in C1, D1, and E1 are joined with a comma and a space ", " as the delimiter.

3. Ignoring Empty Cells

If cell F1 contains "apple", cell G1 is empty, and cell H1 contains "banana", and you want to join them with a space while ignoring the empty cell in G1:

=TEXTJOIN(" ", TRUE, F1, G1, H1)

This will return:

apple banana

Explanation:

• Since G1 is empty and TRUE is used for ignore_empty, the function skips the empty cell and joins only "apple" and "banana" with a space.

4. Including Empty Cells

If you want to include empty cells in the result, you can set ignore_empty to FALSE. For example, if cell I1 is empty:

=TEXTJOIN(" ", FALSE, F1, G1, H1)

This will return:

apple  banana

Explanation:

• The empty cell in G1 is included, which results in two spaces between "apple" and "banana".

5. Concatenating a Range of Cells

You can also use TEXTJOIN with a range of cells. For example, if cells A1:A3 contain "dog", "cat", and "mouse", and you want to join them with a comma:

=TEXTJOIN(", ", TRUE, A1:A3)

This will return:

dog, cat, mouse

Explanation:

• The values from the range A1:A3 are combined with a comma and space ", " as the delimiter.

Advanced Usage:

1. Join Values with Line Breaks

You can use the TEXTJOIN function to join text with line breaks. Use CHAR(10) for a line break in the delimiter (on Windows; for Mac, use CHAR(13)).

=TEXTJOIN(CHAR(10), TRUE, A1, B1, C1)

This will return:

apple
banana
cherry

Explanation:

• The values in A1, B1, and C1 are joined with line breaks, so each word appears on a new line.

(Note: You may need to enable Wrap Text for this to appear correctly in the cell.)

2. Joining with Dynamic Ranges or Arrays

You can also use TEXTJOIN with dynamic arrays or functions that return arrays. For example, to join the results of a filter:

=TEXTJOIN(", ", TRUE, FILTER(A1:A5, A1:A5<>""))

This will join the values from cells A1:A5 where the cell is not empty, separated by commas.

Notes:

• Performance: TEXTJOIN is especially useful when working with large datasets, as it simplifies the process of concatenating multiple items with a delimiter.
• Error Handling: If any of the text1, text2, etc., contain an error (like #N/A or #DIV/0!), TEXTJOIN will return an error unless you use IFERROR or similar error-handling functions.
• Text Length: There is no explicit limit on the number of characters you can join, but Excel does have a maximum limit for text length in a cell (32,767 characters).

Related Functions:

• CONCATENATE (older function): Performs a similar operation but lacks the flexibility of TEXTJOIN in terms of delimiters and ignoring empty cells.
• CONCAT: Another modern alternative that concatenates ranges or text items, but does not allow delimiters or ignoring empty cells like TEXTJOIN.
• JOIN (in Google Sheets): A similar function available in Google Sheets.

The TEXTJOIN function is a powerful and flexible tool for joining multiple text strings in Excel, especially when dealing with large sets of data and when you need to customize the separator or handle empty values.