When working with data in Google Sheets, you may need to combine some specific cells (or combine cells in two columns).
There are multiple ways you can do this using formula in Google Sheets.
In this tutorial, I will show you different ways to combine cells in Google Sheets using simple formulas.
So let’s get started!
Combine Cells without any Separator
Google Sheets has some really simple formulas that you can use to combine cells and columns.
Suppose you have a dataset as shown below and you want to combine columns A and B and get the result in column C.
Below is the formula that will do this:
The above formula uses the ampersand operator (&) to combine values in cell A2 and B2.
You can also use the below CONCATENATE formula to combine the two cells in Google Sheets:
The above CONCATENATE formula simply takes the values that you want to combine and gives you the result.
Combine Cells with Separator (comma/space)
In many cases, you would want to combine cells but have a separator in between. This could be a space character, a comma or a dash.
Again, this can be done with simple formulas by specifying the separator when you’re combining cells.
Suppose you have the dataset as shown below and you want to combine the cells in columns A and B with a dash in between.
Below is the formula that will do this:
In the above formula, I combine three elements, cell A2, the separator (a dash which needs to be in double-quotes) and cell B2.
You can also do the same with the CONCATENATE formula where you need to specify the separator as well within the formula (in double-quotes):
While I have covered an example where I am combining only two cells, you can use the same logic when combining multiple cells
Combine Cells Separated with line breaks
In some cases, you may need to combine cells but have a line break in between the content of each cell.
For example, suppose you have the address data as shown below where each element of the address (name, street, city, and state) is in a different cell and you want to combine these.
But instead of having it as one long text string, you want each of these in a separate line in the same cell.
This can also be done with a simple formula where you will have to use the line feed character as the separator.
Below is the formula that will combine the cells while also inserting a line break between each cell’s content.
Although the above formula would insert the line break, you would only see it when you have the wrap text enabled in the cells. To enable Wrap text, click on Format option in the menu, and then click on Text wrapping –> Wrap
The above formula uses the CHAR(10) formula to get the line feed character as a part of the result.
You can also get the same result using the following TEXTJOIN formula:
The TEXTJOIN formula can take an array of cells and combine all these while having the specified delimiter/separator in between all the cells.
Combine Cells with Text and Numbers (Or Text and Dates)
One area where you may face a challenge is when combining cells that contain text as well as numbers – especially when numbers are formatted in a specific way.
For example, suppose I have the below dataset where I have text data (Department) in column A and number data (Expense) in column B. Also, the expense data in column B is formatted to show percentage value with two decimal points.
Let me combine these two cells using a simple ampersand (with a dash as a separator) using the below formula:
=A2&" - "&B2
Do you see the problem?
The numeric data is shown in decimal points and the formatting has gone away.
This happens as Google Sheets stores numbers in the cell (in the back-end) in a specific way and you can choose to format and show these numbers differently in the cell. For example, when I format the cell to show the value as 42.6%, in the backend it’s stored as 0.426.
Both these values are the same, it’s just the way these are displayed in the cell will depend on the formatting of the cell.
So when you combine these cells (ones that have numbers formatted in a certain way), the formatting goes away.
But what if you want to keep the formatting?
Then you need to modify the formula slightly and use another function in it – the TEXT function.
In the above example, if I want to show the number as a percentage with two decimals, I can use the below formula:
=A2&" - "&TEXT(B2,"#.00%")
In the above formula, I have used the TEXT function to first convert the number into a text with a specific format. And this format is also specified within the formula (in double-quotes). This ensures that you see the data the same way as you see in the cells that you have combined.
If you want, you can also change the format within the formula to show the number in a different format.
Below is another example where I have the names in column A and their date of birth in column B.
Below is the formula that will combine these two cells (with a dash as a separator).
A2&" - "&B2
As you can see, the date gets converted into the underlying number, which is not what you would want when you expect a date of birth value instead.
Below is the formula that can get this sorted:
=A2&" - "&TEXT(B2,"dd-mmm-yyy")
The above formula combines the two cells but also uses the TEXT formula to specify how the date should look like. Note that here you can change the format and make the date display the way you want.
For example, if I want to show the full month name, I would use the below formula;
=A2&" - "&TEXT(B2,"dd-mmmm-yyy")
Or if I want to show the date in the October 12, 1985 format, I can use the below formula:
=A2&" - "&TEXT(B2,"mmmm dd, yyy")
Using the TEXT formula makes sure your number data is presented in a way you want, instead of the underlying value only.
So these are some of the ways you can use to combine cells in Google Sheets. In case you have to combine columns (one or more), you can use similar formulas and apply them for the entire columns.
Hope you found this tutorial useful!
Other Google Sheets tutorials you may like: