Google Sheets provide a couple of useful functions and one of them is CONCATENATE which is very useful if you have content in different cells and you want to combine them. Are you ready to learn how to concatenate in Google Sheets? Let’s take a look.
CONCATENATE Operator in Google Sheets
In order to combine cells in Google Sheets, you may use the the ampersand sign (&) which is the concatenate operator in Google Sheets.
Let’s say you have the first and last names as given below:
If you want to combine the first and last name in a single cell, you may use the following formula:
=A2&" "&B2
The space character has been used in order to have a space between the first name and the last name. Here, you can use any separator (also known as a delimiter), such as a comma, semi-colon, and so on.
Once you press Enter, you’ll get the concatenated result. Simply double-click on the blue point in the right bottom corner to fill in all of the fields below.
CONCATENATE Function in Google Sheets
As previously stated, Google Sheets’ Concatenate tool allows you to easily combine the values from several cells in one cell.
When using the Concatenate function, the syntax is CONCATENATE(Cell1, [Cell2,…]).
- Cell1 – the first string you want to display
- Cell2 – the second string or cell you want to merge to the first one.
To utilize CONCATENATE, open your Google Sheets spreadsheet and select an empty cell. Enter =CONCATENATE(CellA,CellB), but replace CellA and CellB with your own cell references.
You can arrange the cells in whatever order you wish. For instance, if we want Last Name to be the first cell, followed by First Name, we will enter:
=B2&" "&A2
1. Concatenate with Space
If you wish to concatenate two or more cells separated by spaces in Google Sheets, use the following formula:
=A2&" "&B2
In this scenario, the CONCATENATE function takes three arguments: two cells and a delimiter (space, in our case).
2. Concatenate with Comma
If you want to concatenate cells by using a comma delimiter, you may use the following formula:
=CONCATENATE(A2,", ",B2)
3. Concatenate an Array of cells
You can use the following formula to concatenate an array of cells in a single cell:
=ArrayFormula(JOIN(char(10),A2:A6&" "&B2:B6))
You could also use the short formula written below. After writing the formula in your cell, press Control + Shift + Enter, and press Enter again. When you press Control + Shift + Enter, the formula will be appended with ArrayFormula.
=ArrayFormula(JOIN(char(10),A2:A6&" "&B2:B6))
The use of CHAR(10) in the formula adds a line break to the final result (between the cells).
When creating address labels, this method may come in handy, especially when working with names and addresses.
How to concatenate in Google Sheets using JOIN function
If you need to combine data from many cells in a Google Sheet, JOIN is the ideal function to perform.
The JOIN function, unlike CONCATENATE, allows you to automatically place separators between cells, without the need to add each space to the formula. So, the JOIN function in Google Sheets connects the elements of one or more arrays that can be separated by a provided delimiter.
Assume you want to use a space separator to concatenate the cells from A column into a single cell. You may use the following JOIN formula:
=JOIN(",",A2:A6)
How to combine 2 cells in Google Sheets
To learn how to combine 2 cells in Google Sheets, you need to know that there are 2 options: use the CONCATENATE or JOIN function.
The JOIN function, as opposed to CONCAT or CONCATENATE, has a significant advantage: it allows you to use a delimiter as a space that is automatically added after each cell.
For instance, if we wish to concatenate the first names mentioned between cells A2 and A6, we can use the JOIN or CONCATENATE formulas.
=JOIN(",",A2:A6)
=CONCATENATE(A1,",",A2,",",A3,",",A4,",",A5,",",A6)
They both produce the same result. Which do you think is simpler?
CONCATENATE vs CONCAT in Google Sheets
The CONCATENATE function is more advanced than the CONCAT function. It provides greater options for those who want to mix cell data in various forms. CONCAT, for example, doesn’t permit you to add further spaces or text.
