Concatenate cells in Excel: join several cells into just one

Concatenating cells will allow you to greatly speed up your Excel work time. In this post we leave you step by step to learn how to concatenate and we will teach you about its uses.

Key information

When working with databases in Excel, it is common to find that the data is separated into several columns or the data is simply separated. To join all this data in a single cell we must know how CONCATENATE in Excel.

The basics of concatenating in Excel

Concept: Concatenating is joining or combining two or more “strings” of text that are in separate cells into a single cell. If a number or symbol is provided, it will be treated as text.

Aim: In general, when working with information in Excel spreadsheets, the data we want is separated into different columns and we would like it to be in only one. To join the content of these columns we concatenate them.

Methods: There are two ways to concatenate in Excel:

1. Using the CONCAT function

Syntax: CONCAT(Text1, [Text2],…)

Arguments:

Text1: Text that you want to concatenate. It can be a cell, a range (set) of cells, or text written by us. It is mandatory.
[Text2]: Additional text that you want to concatenate. It can be a cell or a range (set) of cells or text written by us. There can be a maximum of 253 text arguments for text elements. It's optional.

2. Using the “&” symbol (ampersand)

Syntax: =Text1&[Text2]&…

Arguments:

Text1: Text that you want to concatenate. It can be a cell or a range (set) of cells. It is mandatory.
[Text2]: Additional text that you want to concatenate. It can be a cell or a range (set) of cells. It's optional.

How to concatenate cells in Excel

Let's think that we have data that separates a person's name into first name, middle name and last name and our goal is to have everything together in a cell, that is, concatenated. As in the following example:

To concatenate using the CONCAT function, we write in cell E3 “=CONCAT(B3;C3;D3)” and we obtain:

Let's remember that with the CONCAT function we can select a range of cells, so we can write “=CONCAT(B3:D3)” and the cells from B3 to D3 will be concatenated in order and we will obtain the same result:

Ninja Tip: There is also the CONCATENATE function which is an old version of CONCAT. The CONCATENATE function still exists for backward compatibility, but it is recommended to use CONCAT as it has certain functionality that CONCATENATE does not have.

Ninja Tip: Remember that you can drag this formula like any other and don't forget that you can use relative, absolute and mixed references with this function.

If we want to use the “&” sign, we write in cell E3 “=B3&C3&D3” and we get the same thing:

Concatenate cells by adding content

Following the example from the previous section, we see that the result that Excel gives us when concatenating is correct, but it would be better to have each cell value separated by a space to make it easier to read. We can do this with the same tools that we already have.

To have a space between each value of each cell using the CONCAT function, we write between double quotes (“”) what we want to concatenate. In this case we want to add spaces between the values of cells B3, C3 and D3, therefore we write “=CONCAT(B3;” “;C3;” “;D3)” and we obtain:

Then, using the “&” sign, apply the same criteria, we add the text we want to incorporate between quotes. In this case, we write “=B3&” “&C3&” “&D3” and we have:

Concatenate text extracts

A very useful tool when concatenating cells are the text extraction functions: EXTRACT, LEFT, RIGHT.

EXTRACT

Its objective is to extract an extract of the text that is in the cell.

Syntax: EXTRACT(text;start_position;number_of_characters)

Arguments:

Text: Cell that contains the characters to be extracted. It is mandatory.
Initial_position: Position of the first character that you want to extract from the text. The start_position of the first text character is 1, and so on. It is mandatory.
#ofCharacters: Specifies the number of text characters that EXTRACT should return. It is mandatory.

LEFT

Its objective is to extract an extract of the text that is in the cell starting from the first character (from the left).

Syntax: LEFT(text, [num_chars])

Arguments:

Text: Cell that contains the characters to be extracted. It is mandatory.
Number_of_characters: Specifies the number of characters you want to extract with the LEFT function. It must be greater than or equal to zero and is 1 by default. If num_of_characters is greater than the length of the text, the function will return all the text. It's optional.

RIGHT

Its objective is to extract an extract of the text that is in the cell starting from the last character (from the right).

Syntax: RIGHT(text, [num_chars])

Arguments:

Text: Cell that contains the characters to be extracted. It is mandatory.
Number_of_characters: Specifies the number of characters you want to extract with the RIGHT function. It must be greater than or equal to zero and is 1 by default. If num_of_characters is greater than the length of the text, the function will return all of the text. It's optional.

These functions can be applied by combining them with CONCAT. For example, to create a code for each customer it will be the initials of their first name, their full last name, followed by a hyphen and their ID. In the case of the first client, it would be: “JJGonzález-33523662”. As seen in the image:

In this way, what we must do is write in cell F3 “=CONCAT(LEFT(B3;1);LEFT(C3;1);D3;”-“;E3)”. In this way, we join the first character of cell B3 and C3 (the initials of the names), then the entire cell D3 (the last name), a hyphen and cell E3 (the customer ID):

You can complement your learning with this Ninja video:

For this type of longer and more complicated concatenations you can use a longer but more comfortable method that avoids errors: Go to the “Formulas” tab, go to “Text” and select CONCAT and a window will open in which you can fill in one by one the items you want to concatenate.

The advantage of this method is that it has a preview of what the final result would look like, so we make sure that the result is the desired one, as seen in the image:

Ninja Tip: We can use this method of opening a window to help us organize what we want to do with many other functions such as YEAH, PAY and many more.

You can watch this Ninja video to complement your learning:

Using the “&” sign it is done in the same way: “=LEFT(B3;1)&LEFT(C3)&D3&”-“&E3” and you get:

UNICHAINS function

When we use CONCAT adding spaces between different cells it will work fine if we have values for each cell that we concatenate, but what happens if there are any blank cells? Let's look at the following example:

As we see, when concatenating the case of Juan José González there is no problem. However, when we drag down and copy the formula for Magdalena Soza we see that the result is “Magdalena··Soza”: there are two spaces together because Magdalena does not have a middle name.

UNICHAINS

To avoid this type of errors when concatenating cells we can use the UNISTRINGS function.

Syntax: CHAINUNIN(delimiter, ignore_empty, Text1, [Text2],…)

Arguments:

Delimiter: A text of one or more characters delimited by double quotes or a cell reference with text that will go between each element that we concatenate. If a number is provided, it will be treated as text. It is mandatory.
Ignore_empty: If TRUE, ignore empty cells. It is mandatory.
Text1: Text element to be concatenated. A text string or an array of strings, such as a range of cells. It is mandatory.
[Text2,…]: Additional text elements to be concatenated. There can be a maximum of 252 text arguments for text elements, including text1. Each of them can be a text string or an array of strings, such as a range of cells. It's optional.

Then, in our example we write “=UNISTRAINS(” “;TRUE;B3:D3). This code means that each cell value is separated by a space but ignores empty cells. We obtain as a result: