The `TEXTJOIN` function in Excel is a powerful tool for concatenating (joining) multiple text strings or ranges of text into a single string, with a specified delimiter between each text element. Introduced in Excel 2016, this function is particularly useful for combining items with a common separator, such as commas, spaces, or any other character.
Here is how you can use the `TEXTJOIN` function:
Syntax
TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)
- delimiter: This is a required argument. It is the character or characters that you want to insert between each text item. For example, it could be a comma (“,”), space (” “), hyphen (“-“), etc.
- ignore_empty: This is a required argument. It is a logical value (`TRUE` or `FALSE`). If `TRUE`, empty cells in the text range or array will be ignored. If `FALSE`, the function will include empty cells, adding consecutive delimiters if necessary.
- text1, [text2], …: These are the text items you want to join. You can provide specific text strings, cell references, or ranges. You can specify more than one text element by separating each with a comma.
Example Usage
Example 1: Joining Text with a Comma
Suppose you have text strings “Apple”, “Banana”, “Cherry” in cells A1, A2, and A3, and you want to join them with a comma.
=TEXTJOIN(", ", TRUE, A1, A2, A3)
This would result in the string: `Apple, Banana, Cherry`.
Example 2: Ignoring Empty Cells
If A1 is “Apple”, A2 is empty, and A3 is “Cherry”, and you use:
=TEXTJOIN(", ", TRUE, A1, A2, A3)
The result would be: `Apple, Cherry`, with the empty cell in A2 being ignored.
Example 3: Using a Range
If you have a list of names in cells A1 through A5, you can combine them with a space separator:
=TEXTJOIN(" ", TRUE, A1:A5)
If any of these cells are empty and you have set the `ignore_empty` argument to `TRUE`, those cells will not affect the result.
Tips
- The `TEXTJOIN` function is particularly useful when dealing with large datasets where manual concatenation becomes impractical.
- If you frequently work with CSV data, `TEXTJOIN` can help in creating a comma-separated list from spreadsheet data quickly.
Unlock Your Potential
Excel
Access
Power BI
