How to Concatenate in Excel

CONCATENATE in Excel is a fairly old function used to merge text values.

As for Excel 2016 and later, the CONCATENATE function has been declared as a compatibility function.

This means that Excel might discontinue the use of this function in its later versions. As a replacement, Excel has come up with a new and improved function—CONCAT.

However, as the current versions of Excel still have the function around, you can use them to merge a variety of text values.

How to CONCATENATE in Excel: Arguments

The CONCATENATE function uses a very simple formula. When constructing a formula using the CONCATENATE function, you can pass text values or references as arguments.

Here is how the CONCATENATE function looks when constructing a formula:

=CONCATENATE(text1, [text2])

CONCATENATE Text in Excel

Let’s start from the basics. You can use CONCATENATE to merge two or more cells within a single cell.

When I say texts, they don’t necessarily have to be alphabets. You can consider any value, that will not be used for mathematical operations as text. An example of numbers that act as text are serial numbers.

Take a look at this spreadsheet.

Here, I have the Batch and Customer numbers in column A and column B respectively. In column C, I have to merge these values to create a unique serial number.

In cell C2, I entered the following formula and then applied it to the entire range:

=CONCATENATE(A2,B2)

CONCATENATE Text and Symbols in Excel

In many cases of merging, you will find the need to put a separator between the combined data. This is true for many cases, such as when concatenating addresses and full names.

Such symbols can be commas, spaces, and even hyphens.

In Excel, when a value is not a reference but simply a text, you will need to enclose it inside double quotes. If you don’t, Excel will identify it as a reference and may return the #NAME? error.

In this spreadsheet, we have three different data tables.

The first one holds the first and last names of individuals, the second their address, and the third their batch and customer number.

In three different cells, we need to concatenate these three values into full names, full addresses, and full serial numbers. Between each of these values, we’ll be inserting space, comma, and hyphen respectively.

In cell D3, I entered the following formula and applied it to the entire range:

=CONCATENATE(B3," ",C3)

In cell D9, I entered the following formula and applied it to the entire range:

=CONCATENATE(B9,",",C9)

In cell D15, I entered the following formula and applied it to the entire range:

=CONCATENATE(B15,"-",C15)

Use CONCATENATE in Excel to Create Sentences

Merging texts using CONCATENATE is not limited to using delimiters. You can use the function to combine values to even create sentences!

Let’s say you have a spreadsheet with the names of 5 kindergarteners in column A. In the adjacent column, you have their favorite colors listed. You can create a sentence out of this information using this format:

(name)’s favorite color is (color)!

We can use CONCATENATE to create such sentences. In cell C2, enter the following formula and apply it to the range:

=CONCATENATE(A2,"'s favorite color is ",B2,"!)

Nest CONCATENATE Inside Other Functions

You can also nest CONCATENATE inside other functions like IF. This will create a conditional statement where CONCATENATE will only merge text if the given statement is true or false.

In this spreadsheet, we have the names of five individuals. Among these users, only two have middle initials. While concatenating, we need to exclude cells from column B if they contain the hyphen symbol.

Here’s the formula we can come up with using IF and CONCATENATE:

=IF(B2<>"-",CONCATENATE(A2," ",B2," ",C2),CONCATENATE(A2," ",C2))

If B2 does not contain a hyphen, CONCATENATE will merge cells A2, B2, and C2. If it does, CONCATENATE will only merge cells A2 and C2.

Alternatives to CONCATENATE in Excel

As I’ve already mentioned before, CONCATENATE is a compatibility function and won’t be around for much longer. Therefore, it would be great if you explored the alternatives to this function.

Ampersand Operator (&)

The Ampersand operator works almost exactly as CONCATENATE. You can only merge individual cells, and not an array using this operator.

Here are a few examples of merging text using the Ampersand Operator

CONCAT Function

You can use CONCAT if you’re on Excel 2016 or later.

The CONCAT function offers everything and more than the CONCATENATE function. CONCAT is an array function. This means you have the flexibility to pass an entire array as your argument for concatenation in this function.

Syntax:

=CONCAT(text1,[text2]...)

Take a look at these examples using CONCAT:

TEXTJOIN Function

You can use TEXTJOIN if you’re on Excel 2019 or later.

TEXTJOIN is yet another function you can use instead of CONCATENATE when you wish to add a delimiter between your combined text.

In addition to adding a delimiter, you can utilize TEXTJOIN if you wish to ignore combining empty cells within the range.

Syntax:

=TEXTJOIN(delimiter,ignore_empty,text1,[text2,[text3]...])

Here’s how you can utilize the TEXTJOIN function in Excel: