CONCAT vs. TEXTJOIN: Which Should You Use in Excel?

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.

In Short: CONCAT vs. TEXTJOIN

Both combine text from multiple cells into one.

According to Cambridge Dictionary, the verb to concatenate means "to put things together as a connected series," and the CONCAT function does exactly this by simply bringing text or cell references together into a continuous string:

=CONCAT(<em>a</em>,<em>b</em>,<em>c</em>)

where a, b, and c are 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.

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

TEXTJOIN does 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.

=TEXTJOIN(<em>w</em>, <em>x</em>, <em>y</em>)

where w is the delimiter, x is TRUE if you want to ignore empty cells (or FALSE if you want to include empty cells), and y is the first of up to 252 references or text strings.

A delimiter is "a mark or symbol used to show the beginning or end of separate elements."

In Practice: CONCAT vs. TEXTJOIN

Let's look further into these with some examples.

CONCAT

In the example below,

=CONCAT(A1,B1,C1)

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

=CONCAT(A1:C1)

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:

=CONCAT(A1," ",A2," ",A3)

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:

• You have people's first names in column A, and their second names in column B, and you want to combine them (separated by a space) in column C.
• You want to automate sentences based on cell values. For example, you might have a person's name in column A and their age in column B, and you want to create sentences for each person that say "[Name] is [age] years old" in column C.
• You need to create composite and compound keys, where you have two lists of reference numbers and want to make a third list that combines the two.

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 type and as a delimiter (the first argument inside the parentheses) with the TEXTJOIN function.

=CONCAT(<em>a</em>,<em>b</em>,<em>c</em>)

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 typed TRUE as the second argument in the formula to tell Excel to exclude blank cells. If we typed FALSE, cell B3 would have read "1 and and 3".

You might use TEXTJOIN in these scenarios:

• You have a list of email addresses on a spreadsheet, and you want to join them together—separated by commas—to copy and paste into Outlook for a batch email.
• You've accumulated a team's results with W, D, and L, and you want to compile all their results into one cell, separated by dashes.
• You want to join large volumes of data together with spaces in between, or you want to include blank cells in your joined data.

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 with CONCAT without 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.

TEXTJOIN is 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.

The above is the detailed content of CONCAT vs. TEXTJOIN: Which Should You Use in Excel?. For more information, please follow other related articles on the PHP Chinese website!