How to Use TEXTJOIN in Excel: Concatenate Cells (2023)
You must have heard of the CONCATENATE function and the concatenation operator (&). Haven’t you?
Both of these are used to merge cell contents in Excel 🤝 But now, we have a third addition to this league too – the TEXTJOIN function.
The TEXTJOIN function is the successor to the old cell merging functions of Excel. And undoubtedly, it is much more powerful and flexible than its predecessors.
It enables users to join together a heap of cells, with or without any delimiter. And that’s not all 😵
To know all about the TEXTJOIN function of Excel – continue reading with us. Download our free sample workbook here to tag along with the guide below.
Table of Contents
How to use TEXTJOIN to combine cells in Excel
The TEXTJOIN function merges the content of multiple cells (or ranges). And in the process of merging, you can add any delimiter between the merged content.
It also enables users to include or ignore any empty cells in the final merged cell.
Let’s look into an example of how to use the TEXTJOIN function in Excel🚀
Here’s an image that contains sub-parts of multiple addresses in multiple columns.
Let’s combine each of these sub-parts to reach the complete address. And for that, we need the TEXTJOIN function.
- Begin writing the TEXTJOIN function as follows:
= TEXTJOIN (
- Specify the delimiter for the merged cell as the first argument
The first argument of the TEXTJOIN function specifies the delimiter to be used in the merged cell content. You can specify any text or number as the delimiter (separator). But this must be enclosed in double quotation marks 👀
Or, you can also refer to a particular cell containing the value you want as the separator.
We are specifying a comma as our delimiter here:
= TEXTJOIN (“,”
- As the ignore_empty argument, write:
TRUE: If you want the TEXTJOIN function to ignore any empty cells in the resulting string
FALSE: If you don’t want the TEXTJOIN function to ignore any empty cells in the resulting string.
We are going with the first option (TRUE) for now 🥇
= TEXTJOIN (“,”, TRUE
- Supply the values to be merged as the Text1 argument.
The TEXT1 argument is a required argument where you specify the first value to be merged. This could be a text string (enclosed in double quotation marks). Or a reference to a cell or a range of cells that you want to be merged.
= TEXTJOIN (“,”, TRUE, A2:D2)
We want the sub-parts of each address merged. As all these sub-parts lie in adjacent cells, we have referred to the entire cell range i.e. A2 to D2 as the Text1 argument.
- Hit “Enter” and there you go!
The TEXTJOIN function combines the content of each cell in the given range. And after each cell’s content, the delimiter (comma) is placed 🤩
- Drag and drop the same to the whole list.
Note that for the third address, Cell B4 was empty. Excel didn’t include it in the merged cell content.
This is because we had set the ignore_empty argument to TRUE.
After the Text1 argument, there’s another optional argument for Text2, Text3, and so on. For each of these arguments, you must specify the values/cells/ranges that you want to be merged.
For example, we could have written the above function as:
= TEXTJOIN (“,”, TRUE, A2, B2, C2, D2)
Where A2 is Text1, B2 is Text2, C2 is Text3 and D2 is Text 4. The merged content however would be the same as above.
You can specify a whole cell range as a single Text argument if you want to merge adjacent cells as above 🧾
But if you want to merge multiple cells, text strings, or ranges that are non-adjacent – you must use multiple text arguments to specify each cell or range.
TEXTJOIN vs CONCATENATE functions
Excel already had the CONCATENATE function to merge different cells. Then what was the need for introducing the text function to merge cells?
The TEXTJOIN function offers enhanced features that the CONCATENATE function lacks.
Here are some aspects that differentiate the TEXTJOIN and the CONCATENATE function:
In the CONCATENATE function, you need to specify the Delimiter to be used between every two cells.
For example, let’s merge the sub-parts of the address using the CONCATENATE function.
To merge them, you’d need to write the CONCATENATE function as follows:
= CONCATENATE (A2, “,”, B2, “,”, C2, “,”, D2)
And here are the results 👇
With the CONCATENATE function, you must individually specify each cell to be merged.
And a delimiter after each cell. This makes the writing of the function ineffective.
Also, if you have a good number of cells or texts to be merged – it might take you quite some time to write the whole function.
On the contract, the TEXTJOIN function allows you to specify the delimiter once.
= TEXTJOIN (“,”, TRUE, A2:D2)
With the CONCATENATE Function, you can specify a different delimiter for every two cells.
= CONCATENATE (“Johnson”, “ ” ,“Scholes”, “, “, LA)
The results would be:
Johnson Scholes, LA
A space character between Johnson and Scholes. And a comma delimiter between Scholes and LA.
This can be difficult (but not impossible 🚩) to achieve with the TEXTJOIN function. Particularly if there is a mixed variety of delimiters to be used.
Specify each cell reference individually
Another drawback of the CONCATENATE function is that you cannot specify a whole cell range in it.
For example, if we write the following formula:
Here’s how the combined data would look like:
The cell values are not merged but split across multiple cells in Excel.
This brings the unease of users having to specify every cell (or text value) individually 🙅♀️
Whereas, using the TEXTJOIN function, you can specify one cell range or multiple ranges seamlessly.
= TEXTJOIN (“,”, TRUE, A2:D2, A3:D3)
That’s it – Now what?
That’s all about the TEXTJOIN function of Excel. You now know how to pull together the data from different parts of your spreadsheet in one go.
Without a doubt, the TEXTJOIN function makes an excellent addition to the Excel functions library. It is well-thought, powerful, and flexible 💪
But even then, it is only a small function of Excel’s very vast function library. And there’s much more for you to unveil yet.
Some of the top functions from the Excel function library include the VLOOKUP, SUMIF, and IF functions.
Want to learn them already? Enroll in my 30-minute free email course here that teaches you all about these functions.
The TEXTJOIN function makes a great addition to the league of cell-merging functions. However, in some situations, the CONCATENATE function might prove more useful.
Which are those situations? And how to use the CONCATENATE function of Excel? Learn that here.