How to Use TEXTJOIN in Excel: Concatenate Cells (2024)

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.

Sub-parts of different addresses

Let’s combine each of these sub-parts to reach the complete address. And for that, we need the TEXTJOIN function.

  1. Begin writing the TEXTJOIN function as follows:

= TEXTJOIN (

Writing the TEXTJOIN function
  1. 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.

Kasper Langmann, Microsoft Office Specialist

We are specifying a comma as our delimiter here:

= TEXTJOIN (“,”

Specifying comma as delimiter
  1. 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

Third argument set to TRUE
  1. 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.

Kasper Langmann, Microsoft Office Specialist

= TEXTJOIN (“,”, TRUE, A2:D2)

The Text1 argument

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.

  1. Hit “Enter” and there you go!
TEXTJOIN combine multiple cells in Excel

The TEXTJOIN function combines the content of each cell in the given range. And after each cell’s content, the delimiter (comma) is placed 🤩

  1. Drag and drop the same to the whole list.
Merged Cell values

Note that for the third address, Cell B4 was empty. Excel didn’t include it in the merged cell content.

Empty string ignored

This is because we had set the ignore_empty argument to TRUE.

Pro Tip!

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:

Separate Delimiters:

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.

Sub-parts of different addresses

To merge them, you’d need to write the CONCATENATE function as follows:

= CONCATENATE (A2, “,”, B2, “,”, C2, “,”, D2)

Writing the CONCATENATE function

And here are the results 👇

CONCATENATE function combine data

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)

Excel combines the sub-parts of the address

Pro Tip!

With the CONCATENATE Function, you can specify a different delimiter for every two cells.

For example:

= 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:

CONCATENATE (A2:D2)

Writing a cell range in CONCATENATE

Here’s how the combined data would look like:

CONCATENATE functions combine text

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)

TEXTJOIN merges multiple cell ranges

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.

Other resources

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.

Frequently asked questions

The TEXTJOIN function is a relatively newer Excel function. It is only available to users of Excel 2019, Excel 2021, and Office 365.

Instead of the TEXTJOIN function, you can use the CONCATENATE or the CONCAT function to merge cell contents.

Or you can even use the concatenation operator (an ampersand &) to merge cell contents in Excel.

The TEXTJOIN function performs the same job as the CONCATENATE function. However, it is way more powerful, flexible, and efficient in terms of application.

It allows users to:

  • Merge multiple cell ranges in one go.
  • No need to specify the delimiter over and over again.
  • Choose to include or ignore empty cells in the resulting cell.