How to Concatenate Cells with a Custom Delimiter in Excel

You've got a column of names, cities, or product codes in Excel, and you need to join them into one cell with a specific separator. Maybe a comma, a pipe, or a semicolon. Excel gives you a few ways to do this, and once you know the right formula, it takes about ten seconds.

The Old Way vs. The Right Way

For years, people used the CONCATENATE function or the & operator to join cells. It works, but it's tedious. If you have 20 cells, you're writing a very long formula and manually adding your delimiter between each reference.

The better approach is TEXTJOIN, which was introduced in Excel 2019 and Microsoft 365. It handles everything in one clean formula.

How TEXTJOIN Works

The syntax is straightforward. Here's what each argument does:

1. delimiter: The character or string you want between each value (a comma, pipe, space, etc.)
2. ignore_empty: Set to TRUE to skip blank cells, or FALSE to include them.
3. text1, text2, ...: The cells or range you want to join.

So a basic formula looks like this:

= TEXTJOIN(", ", TRUE, A2:A10)

That joins every value in A2 through A10 with a comma and a space, skipping any empty cells. Simple and clean.

Common Delimiter Examples

Your custom delimiter can be almost anything. Here are some common use cases to give you a starting point.

What If You Don't Have TEXTJOIN?

If you're on an older version of Excel (2016 or earlier), TEXTJOIN isn't available. You have a couple of options.

• Use the & operator: =A2&","&A3&","&A4 (gets ugly fast with many cells)
• Use a helper column where each row builds on the previous one, adding the delimiter as it goes.
• Upgrade to Microsoft 365, which includes TEXTJOIN and other modern functions.
• Export your data and handle the joining outside Excel using an online delimiter converter.

Joining Cells Across Multiple Columns

TEXTJOIN isn't just for a single column. You can pass multiple ranges or individual cells as separate arguments. For example, to join first name, last name, and city with a pipe between each:

=TEXTJOIN(" | ", TRUE, A2, B2, C2)

You can also nest other functions inside TEXTJOIN. That's where it gets really powerful. Wrapping an IF function inside lets you join only cells that meet a condition, all without a helper column.

Working With the Output Outside Excel

Once you've concatenated your data, you often need to paste it somewhere else, like a database, a config file, or another tool. The delimiter you choose matters a lot at that point.

If you end up with data that uses the wrong separator for your next step, you don't have to go back and rewrite the formula. You can paste the output into a comma to pipe converter and switch it in seconds. It's much faster than editing a spreadsheet formula and re-copying everything.

Key Points

• TEXTJOIN is the modern, clean way to concatenate cells in Excel with a custom delimiter.
• The syntax is: =TEXTJOIN(delimiter, ignore_empty, range).
• Set ignore_empty to TRUE to avoid double delimiters from blank cells.
• Older Excel versions don't support TEXTJOIN, so use the & operator or an external tool instead.
• If you need to change the delimiter after the fact, a free online tool is often faster than rewriting your formula.

Quick Reference Before You Go

Here's a one-line summary for each scenario you're likely to hit. Bookmark this or paste it into your notes.

1. Join a range with commas: =TEXTJOIN(",", TRUE, A2:A50)
2. Join specific cells with a pipe: =TEXTJOIN("|", TRUE, A2, B2, C2)
3. Join with a space (names): =TEXTJOIN(" ", TRUE, A2, B2)
4. Skip blanks automatically: always set the second argument to TRUE
5. Need a different delimiter afterward: use a delimiter converter to swap it without touching Excel