The TEXTJOIN function in Excel is used to concatenate (join) multiple text strings into one string with a specified delimiter separating each text string like a comma. This function is particularly useful when you want to combine text from different cells and include a separator between them.
Syntax
TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)
The TEXTJOIN function syntax has the following arguments:
- delimiter: The character(s) you want to use to separate the text strings (e.g., a comma, space, or any other character).
- ignore_empty: A boolean value (TRUE or FALSE). If TRUE, empty cells are ignored. If FALSE, empty cells are included in the result.
- text1, [text2], …: The text strings or ranges you want to join.
Example
Let’s say you have the following data in cells A1 to A3:
- A1: “Apple”
- A2: “Banana”
- A3: “Cherry”
You want to join these values into a single string, separated by a comma and a space. You can use the TEXTJOIN function as follows:
=TEXTJOIN(", ",TRUE,A1,A2,A3)
This formula will return:
Apple, Banana, Cherry
Another Example
If you have a range of cells from A1 to A5 and you want to join them with a hyphen, ignoring any empty cells, you can use:
=TEXTJOIN("-",TRUE,A1:A5)
This will concatenate all non-empty cells in the range A1:A5, separated by a hyphen.
TEXTJOIN Without Duplicates
You can remove duplicates when using TEXTJOIN. An example of this would be:
=TEXTJOIN(", ",TRUE,IF(MATCH(A2:A9,A2:A9,0)=MATCH(ROW(A2:A9),ROW(A2:A9)),A2:A9,""))
Other MS Excel Function You Must Know