If you have content in different cells and you want to combine it, Google Sheets provide a couple of useful functions – such as CONCATENATE and JOIN.
In this tutorial, I will cover examples of how to concatenate in Google Sheets.
Before we jump to examples, let’s first learn about the concatenate operator and the concatenate function.
CONCATENATE Operator in Google Sheets
Ampersand sign (&) is the concatenate operator that you can use to combine cells in Google Sheets.
For example, suppose you have the first name and the last name as shown below:
You can use the following formula to combine the first and the last name.
=A2&" "&B2
Note that I have used a space character in between the reference for the first and the last name as I want these words to be separated by a space character. You can use any separator (also called delimiter) such as comma, hyphen, semi-colon, etc.
While ampersand concatenates operate works great, it’s useful only when you have a couple of cells to combine. In case you have a lot of cells that you need to concatenate in Google Sheets, it’s better to go for the functions.
CONCATENATE Function in Google Sheets
Concatenate function in Google Sheets allows you to quickly combine the values in cells.
Here is the syntax of the Concatenate function:
CONCATENATE(string1, [string2, …])
- string1 – this is the initial string (first string).
- string2 – this is the second string that you want to concatenate to the first one. You can specify
Now let’s see a couple of examples of using the CONCATENATE function in Google Sheets.
Example 1 – Concatenate with Space
Suppose you have the data set of first name and last name and you want to join these with space in between.
Here is the formula that can get this done:
=CONCATENATE(A2," ",B2)
Note that the function has 3 arguments – the first name, a space character in double quotes, and the second name.
Example 2 – Concatenate With Comma
Suppose you have the same dataset of names, but instead of combining it with space in between, you want it in the following format: last name, first name.
Here is the formula that can get this done:
=CONCATENATE(B2,", ",A2)
Example 3 – Concatenate With a Running Number
Concatenate function can also be useful when you want to combine the text with a running number.
For example, in the dataset below, you want to get add a running number in front each name. So for the first name, it will be 01 – John Spike, for the second name it should be 02 – Brad Connor, and so on.
Here is the formula that can get this done:
=CONCATENATE(ROW()-1," - ",A2," ",B2)
Example 4 – Concatenate an Array of Cells
Suppose you have the same data set of names (as shown below):, however, in this
However, in this case, you want to combine the names and list these in a single cell (each name in a new line).
The following formula can get this done:
=JOIN(char(10),A2:A6&" "&B2:B6)
Note that this is an array formula so use Control + Shift + Enter (hold the Control key and the Shift key and then press Enter). When you hit Control + Shift + Enter, it will automatically append ArrayFormula to the formula.
The formula uses CHAR(10) which would add a line break to the result of the formula.
This technique can also be useful when you have addresses with different parts in different cells in a row (such as house number in one cell, the street name on another, the city name on another and so on, and you want to combine it to create address labels.
You May Also Like the Following Google Sheets Tutorials:
