TEXTJOIN
The TEXTJOIN function is used to combine multiple text strings into one string, with a specified delimiter separating each text value. It's super handy for merging a bunch of cells into a single cell efficiently.
Syntax
=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...) Arguments
| Argument | Required | Description |
|---|---|---|
| delimiter | Yes | The separator to use between each text item (e.g., a comma, space, or any other character). |
| ignore_empty | Yes | A TRUE or FALSE value indicating whether to ignore empty cells or not. |
| text1 | Yes | The first text item to join. This can be a cell reference or a direct text value. |
| text2 | No | Additional text items to join. You can add as many as you like. |
About
TEXTJOIN is a function in Excel that lets you merge multiple text values into one cohesive string, with a specified delimiter thrown in between each value. It's perfect for when you have a list of values in different cells and you want to amalgamate them into a single cell, using a comma, space, or any custom character to separate them. What's particularly useful about TEXTJOIN is its ability to ignore empty cells if you want it to, making your text strings cleaner and avoiding unnecessary delimiters.
Examples
Suppose you have the names 'John', 'Jane', and an empty cell in cells A1, A2, and A3, respectively. You want to combine these names into a single string, separated by a comma and space. You can use the following formula:
=TEXTJOIN(", ", TRUE, A1, A2, A3)
This will return the string: 'John, Jane'.
Imagine you have the words 'Excel', 'is', 'awesome' in cells B1, B2, and B3 respectively. You want to concatenate these words with a space between them. Use the formula:
=TEXTJOIN(" ", FALSE, B1, B2, B3)
This will return: 'Excel is awesome'.
If cells C1:C5 contain 'Hello', '', 'World', '', '!', and you want to combine them with no separator, while ignoring empty cells, you can use:
=TEXTJOIN("", TRUE, C1:C5)
This will produce: 'HelloWorld!'
=TEXTJOIN(", ", TRUE, A1, A2, A3)
This will return the string: 'John, Jane'.
Imagine you have the words 'Excel', 'is', 'awesome' in cells B1, B2, and B3 respectively. You want to concatenate these words with a space between them. Use the formula:
=TEXTJOIN(" ", FALSE, B1, B2, B3)
This will return: 'Excel is awesome'.
If cells C1:C5 contain 'Hello', '', 'World', '', '!', and you want to combine them with no separator, while ignoring empty cells, you can use:
=TEXTJOIN("", TRUE, C1:C5)
This will produce: 'HelloWorld!'
Tips & notes
One thing to watch out for is to set
ignore_empty appropriately based on whether you want those empty cells to be part of your joined text or not. Also, remember you can use ranges instead of listing each cell individually. That can make your formula much cleaner.Common questions
Can I specify multiple delimiters for the TEXTJOIN function?
No, TEXTJOIN allows for only a single delimiter. If you need different delimiters, you might need to use nested functions or additional logic.
What happens if I set `ignore_empty` to FALSE?
If you set
ignore_empty to FALSE, TEXTJOIN will include empty cells in the final string, which means you'll see multiple delimiters together where there were empty cells.Is there a limit to how many text items I can join with TEXTJOIN?
There's no specific limit to the number of arguments you can pass to TEXTJOIN, but practical performance limits like maximum formula length or memory capacity might come into play for extremely large data sets.
Can TEXTJOIN be used with arrays or ranges?
Absolutely! You can use TEXTJOIN with arrays or ranges, which makes it a lot more versatile for joining large sets of data without having to list each cell reference individually.