Manually combining data from two or more cells into another cell would take time and inevitably result in errors, especially if you’re dealing with lots of data. Excel is designed to automate time-consuming tasks, and the CONCAT and TEXTJOIN functions are textbook examples of this in action.

CONCAT and TEXTJOIN were added to Excel in 2019, so if you’re using an older version, you might not have access to these functions.

An Excel spreadsheet containing the CONTACT function and the resultant output.

In Short: CONCAT vs. TEXTJOIN

Both combine text from multiple cells into one.

According toCambridge Dictionary, the verbto concatenatemeans “to put things together as a connected series,” andthe CONCATfunctiondoes exactly this by simply bringing text or cell references together into a continuous string:

wherea,b, andcare the text or cell references you want to combine into one cell. Enclose any text values (including punctuation and spaces) in double-quotes. You can have up to 252 references or text strings here.

An Excel spreadsheet containing the CONTACT function with spaces and the resultant output.

If blank cells are referenced in the CONCAT formula, they are ignored in the output unless placed between commas.

TEXTJOINdoes the same job as CONCAT, but a delimiter allows you to choose what to insert between each item in the concatenation. TEXTJOIN also gives you the option to include or exclude empty cells.

An Excel spreadsheet containing the TEXTJOIN function to add the word ‘and’ between each argument.

wherewis the delimiter,xis TRUE if you want to ignore empty cells (or FALSE if you want to include empty cells), andyis the first of up to 252 references or text strings.

Adelimiteris “a mark or symbol used to show the beginning or end of separate elements.”

An Excel spreadsheet containing the TEXTJOIN function with spaces excluded.

In the example below,

combines all the data in cells A1 to C1 into cell B3. We could also have typed

as the cells are contiguous, and we didn’t require any values, punctuation, or spaces between the numbers in cell B3.

Here’s another example:

which would add a space in between each value pulled from cells A1 to C1. Remember, each argument needs to be separated by a comma, and any text values (including punctuation and spaces) need to go in double-quotes.

You might use CONCAT in these scenarios:

CONCAT is replacing the CONCATENATE function, which is gradually being phased out. With CONCATENATE, you couldn’t work with arrays (for example, A1:A40), only individual cell references (for example, A1,A2,A3…). So, it’s best to avoid CONCATENATE altogether, and go with CONCAT from now on.

TEXTJOIN

Let’s take the same example, but this time, we want to add the word “and” between each number.

Rather than using CONCAT and repeating “and” between each value, we can typeandas a delimiter (the first argument inside the parentheses) with the TEXTJOIN function.

Notice the spaces on each side of the word “and” in the formula. Excel doesn’t add spaces here automatically, so you need to add them manually. Otherwise, everything would be squeezed together and look unusual.

If we were to use the same formula, but remove the number 2 from cell B1, here’s what we’d see:

This is because we typedTRUEas the second argument in the formula to tell Excel to exclude blank cells. If we typedFALSE, cell B3 would have read “1 and and 3”.

In Sum: CONCAT vs. TEXTJOIN

So, which one should you use?

If you want to simply combine values from numerous cells into a single cell, you can do that withCONCATwithout having to worry about delimiters and blank cells. Just bear in mind that unless you add spaces or punctuation manually, all the values will be squished together without clear definitions between each. You may also prefer to use CONCAT if you want to have different delimiters between each concatenated value.

TEXTJOINis better for defining what will go in between each linked value, such as words, spaces, or punctuation. Rather than using CONCAT and manually inserting a comma, for example, between each value, you can do this more efficiently by adding a delimiter as the first argument inside the parentheses. This is particularly handy if you’re handling large amounts of data. Just remember that you can only specify one delimiter when using TEXTJOIN, meaning you’ll end up with the same value or symbol placed between each item joined together.

As well as those described above, there are other ways to join values together in Excel. First, you can use the ampersand (&) symbol between cell references in a formula. Alternatively,Excel’s Flash Fill tool(keyboard shortcut: Ctrl+E) spots patterns and applies them to the rest of the series.