Excel Concatenate function examples and common errors

how to use the concatenate function

The CONCATENATE function in Excel enables a user to join two or more cells into one. We often need to join the first and last name into a full name or street, house number and city into a full address. One of the easiest ways to complete this is using the Concatenate function. In this tutorial, we will see how to use the function to concatenate texts, cells references, function results, etc.

Formula Syntax Explanation

The syntax of the CONCATENATE function in Excel looks like:

=CONCATENATE(text1, [text2], [text3], …)

The parameters of the function are:

text1, [text2], [text3]

These are texts which we want to concatenate, separated with a comma. Only one text is mandatory, while up to 255 texts can be passed to the function. The function accepts all kinds of data, including numbers, dates, other functions, cells references, etc. As a result, the function will return a text, regardless of the input data formats.

Please note that from Excel 2016, the function CONCAT is introduced instead of the CONCATENATE function. These functions have the same syntax and almost similar results. The only difference is that the CONCAT function works with ranges, which will be explained more later. However, we will explain all the examples on the CONCATENATE function, because of the compatibility with the older Excel versions.

Basic usage of the CONCATENATE function

We will first look at the most simple example of the function, which includes joining two texts. In the example, we want to concatenate “Mike“ and “Johnson” in the cell B3 to get the full name. The formula is:

=CONCATENATE(“Mike “, “Johnson”)

Image 1. Concatenation of two texts
Image 1. Concatenation of two texts

As you can see in Image 1, all the texts which we want to join, need to be put under quotations (“). As a result, we get “Mike Johnson” in cell B3.

Now we will see how to do the same concatenation, but with cell references instead of texts. In this example, the first name is in the cell B3, while the last name is in the cell C3. The result of the function is in the cell D3. The formula is:

=CONCATENATE(B3, C3)

Image 2. Concatenation of two cells
Image 2. Concatenation of two cells

As we can see in Image 1, values from B3 and C3 are concatenated into “MikeJohnson” in the cell D3. In this example, we need to add a space between the first and last name to make the output valid.

Concatenate cells with spaces and other symbols

In order to add a space between two cells, we need to put a space under the quotations (“ “) as a parameter of the function. The formula is:

=CONCATENATE(B3, ” “, C3)

Image 3. Concatenation of two cells with space
Image 3. Concatenation of two cells with space

Now the output of the function in D3 is the full name with a space between the first and last name (“Mike Johnson”).

Similarly to this example, we can join two cells with a comma between. In this case, we need to put a comma instead of space. The formula looks like:

=CONCATENATE(B3, “, “, C3)

Image 4. Concatenation of two cells with a comma
Image 4. Concatenation of two cells with a comma

Image 4. Concatenation of two cells with a comma

The result of the function in the cell D3 is “Mike, Johnson”. Similarly to this example, we can add any separator between the two cells.

Concatenate a text and cell values

As we saw in the previous example, the CONCATENATE function can work with both cells and texts. Now we will see how to combine cells and text in the same formula. In the cell D3 we want to concatenate the text “Fullname:“ and cells B3 and C3 with a space. The formula is:

=CONCATENATE(“Fullname: “,B3,” “, C3)

Image 5. Concatenation of cells with a text
Image 5. Concatenation of cells with a text

The result of the function in D3 is “Fullname: Mike Johnson”. As you can see in Image 5, the function can combine texts and cells in concatenation.

Concatenate cells with a line break

Another possible option is to concatenate cells with a line break. This is possible by using the CHAR function which takes an ASCII code and returns a special character. In our case, the ASCII code for a line break is 10. Before applying the formula, we need to enable wrap text option. To do this, click on the cell with the formula (D3) and then click on the Wrap text icon in the “Alignment group” of the Hometab. The formula is:

=CONCATENATE(B3, CHAR(10), C3)

Image 6. Concatenation of cells with a line break
Image 6. Concatenation of cells with a line break

As a result, the first name is in the first row of the cell D3, while the last name is in the second row. This is also the example of using the function as a parameter of the CONCATENATE function.

Concatenate a range of cells

As we mentioned in the formula explanation, the CONCATENATE function can’t take a range of cells as a parameter. Instead, we have to select every cell from a range as a separate parameter. In the next example, we want to concatenate all the names from the range B3:B8. To do this we need to type “=CONCATENATE(“ in the formula bar, hold CTRL and click on every cell in the range. The formula looks like:

=CONCATENATE(B3,B4,B5,B6,B7,B8)

Image 7. Concatenating the range with the CONCATENATE function
Image 7. Concatenating the range with the CONCATENATE function

As a result, we get all the names from the range B3:B8 concatenated in the cell D3.

If you have Excel 2016 or newer, another way to do the same is to use the CONCAT function. This function takes a range as a parameter. If we want to concatenate all the values from the range B3:B8, we need to type “=CONCAT(“ in the formula bar and select the range. The formula looks like:

=CONCAT(B3:B8)

Image 8. Concatenating the range with the CONCAT function
Image 8. Concatenating the range with the CONCAT function

The result in the cell D3 is the same as in the previous example.

Common error when using the CONCATENATE function

The CONCATENATE function may return an error if one of the parameters is a function. In this case, if the result of the function is an error, the CONCATENATE will also return an error. Let’s explain this using an example. In the cell E3, we want to concatenate values from B3, C3 and D3, separated by spaces. The cell C3 contains #VALUE! error as a result of a function:

Image 9. The CONCATENATE function error
Image 9. The CONCATENATE function error

As you can see in Image 9, the result of the CONCATENATE function in the cell E3 is #VALUE! error, as the consequence of the error in C3.

In order to prevent the error when using the function as the parameter of the CONCATENATE function, we can use the IFERROR function. This function checks if a selected cell contains an error and returns a given value instead of an error. The formula is:

=CONCATENATE(B3, “ “, IFERROR(C3, “”), “ “, D3)

Image 10. CONCATENATE function error
Image 10. CONCATENATE function error

As you can see from the formula, we check the value in the cell C3 and if it’s an error, we return an empty string (“”). As a result of the formula, we get “Mike Johnson”, because the error value from the cell C3 is replaced by an  empty string.

Final thoughts

All in all, the CONCATENATE function could prove very useful in many cases but if you don’t need cross-version compatibility and are using the latest (2016) version of Excel, we recommend using the CONCAT function instead. It provides added functionality and reduces the length of the function name and its arguments.

Leave a Reply

Your email address will not be published. Required fields are marked *