How to Use Excel’s TEXTJOIN Function: Combine Multiple Text Strings with Delimiters

スポンサーリンク
スポンサーリンク

About the TEXTJOIN Function

Overview of TEXTJOIN

Combine Multiple Text Strings into One Excel Function/Text Operations

=TEXTJOIN(Delimiter, IgnoreEmpty, Text1,…)

Summary: Combines text strings with a specified delimiter. Use TRUE for IgnoreEmpty to skip blank cells.

Supported Versions: 365 2021

  • Unlike CONCATENATE or CONCAT, TEXTJOIN allows you to specify a delimiter.
  • You can use a colon (:) to specify ranges for concatenation.
  • Since Office 2016 is still widely used, avoid using TEXTJOIN when sharing files with users of older Excel versions.

When to Use TEXTJOIN

  • To join multiple cells into a single string separated by commas.
  • To combine cells with spaces as delimiters.

How to Use TEXTJOIN

  A B C D E
1 A B    
2 D A,B,,D

=TEXTJOIN(“,”, FALSE, B1:C2)

If TRUE is used, the result will be “A,B,D”.

If an empty string (“”) is used, the result will be “ABD”.

 
3
4

TEXTJOIN Is Unavailable!

  • TEXTJOIN is only available in Excel 365 and 2021. It is not supported in 2019 or earlier versions.

Alternatives to TEXTJOIN

  • You can use CONCATENATE to manually add delimiters or prepare separate cells with the desired delimiter for combination.

Combining Text with Line Breaks

  • To insert line breaks, specify CHAR(10) as the delimiter in the function.
  • Ensure the cell (e.g., C1 below) has “Wrap Text” enabled under Home → Alignment → Wrap Text for proper display.
  • Example of combining text with line breaks:
A B C D
1 New York Manhattan

New York

Manhattan

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

=A1 & CHAR(10) & B1 can also be used.

 
2

 

3

 

Summary of TEXTJOIN

  • The TEXTJOIN function is a powerful tool for combining multiple text strings or ranges with a specified delimiter. It allows for efficient concatenation by ignoring empty cells if needed. However, careful configuration is required to avoid unexpected results.
  • Compared to CONCATENATE or CONCAT, TEXTJOIN provides more flexibility and is ideal for combining data into single strings efficiently.