How to Combine Cells in Google Sheets [Easy Guide]

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. However, using merge cells is not the best way to combine cells in Google Sheets since it only retains the values in the first cell and it changes the layout of the spreadsheet.

The best way to have Google Sheets combine text from two cells is to use formulas. In this tutorial, I will show you how to combine cells in Google Sheets using a few simple formulas.

So let’s get started!

A Step-By-Step Guide to Combine Columns In Google Sheets

We have several examples of formulas that combine cells and columns, and the methods you can use them remain largely the same, so let’s take a look at the steps you can take. Either method for Google Sheets to combine columns into one will yield correct results for these formulas. It boils down to personal preference.

Using the Fill Handle

1. Select the first empty cell that you want the combined data to show in
2. Enter the example formula
3. Click and drag the Fill down handle over the other cells you wish to apply the formula to. The formula will be copied to the selected cells.

Using an ARRAYFORMULA

You can use an ARRAYFORMULA instead of the fill handle to choose the cells or columns to combine. ARRAYFORMULAS apply arrays to non array functions. To use them, you simply have to wrap the formula with =ARRAYFORMULA()

For instance, in the following example, the formula is =A2&B2. To use ARRAYFORMULA instead of the fill handle you could type.

=ARRAYFORMULA(A2:A8&B2:B8)

As you can see, the exact cell range is specified rather than an individual row.

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. You could specify the cells with an ampersand in between.

Below is the formula that will do this:

=A2&B2

The above formula uses the ampersand operator (&) to combine values in cell A2 and B2.

How to Merge Columns in Google Sheets with CONCAT Function

One really useful function is the Concat sheets function. You can also use the CONCATENATE function to combine two cells in Google Sheets.

The Google Sheets CONCAT function has the following syntax:

CONCAT(string1, [string2, ...])

The Google Sheet concat strings function specifies the values that are to be joined by the formula.

For example, in our example sheet, we can use the formula =CONCAT(A2,B2) to get the combined results as shown below:

The above CONCAT function simply takes the cell values that you want to combine and gives you the result. You can then drag the box to the cells below, and the formula will be copied into the selected cells.

We can also use the CONCATENATE formula where you need to specify the separator as well within the formula (in double quotes):

In our example above we will use a comma as the separator and replace the CONCAT formula with the one below:

=CONCATENATE(A2,",",B2)

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:

=A2&"-"&B2

In the above formula, I combine three elements, cell A2, the separator (a dash which needs to be in double quotes), and cell B2.

The separator can also be a comma. For example:

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):

=CONCATENATE(A2,"-",B2)

While I have covered an example where I am combining only two cells, you can use the same logic when combining multiple cells

Combining Cells in Google Sheets Separated With Line Breaks

In some cases, you may need to have Google Spreadsheet combine cells with 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.

=A2&char(10)&B2&char(10)&C2&char(10)&D2

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 the 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:

=TEXTJOIN(CHAR(10),,A2:D2)

The TEXTJOIN formula can take an array of cells and combine all these while having the specified delimiter/separator in between all the cells.

Use Google Sheets to 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.

How to Combine Cells in Google Sheets Using the UNIQUE Function

UNIQUE is a native array compatible formula, so you don’t need to use an ARRAYFORMULA to use it to combine cells. Instead, you could use the following syntax:

=UNIQUE({Array_1;Array_2})

Check out the below example to see it in action:

As you can see column C has the values of columns A and B. Using unique also removes any duplicates which may be handy in many situations.

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 to the entire columns.

Read more: How to Use the UNIQUE Function.

Frequently Asked Questions

How Do I Combine Cells in Google Sheets?

Google Sheets can combine text from two cells with  several different formulas, but the simplest is just putting the & symbol between two cell references in an empty cell, like so: =A2&B2

How Do I Combine Data From 3 Cells Into 1?

You can use any of the above methods to combine data from 3 cells into one by adding a third cell reference. For example, =A2&B2&C2 would combine those cells.

How Do You Use CONCATENATE?

CONCATENATE combines cells and lets you choose a separator, you just have to provide the cell references like in the following example:

=CONCATENATE(A2,"-",B2)

This would provide the date from A2 and B2 with a dash in between.

Conclusion

In this article, we have shown you several different ways to combine cells in Google Sheets. You can use functions such as the CONCATENATE function, ARRAYFORMULA, and the UNIQUE function. You can also use separators or the fill handle.

If you found this article useful, you can check out how to append text in Google Sheets.

If you found this tutorial on how to combine cells in Google Sheets useful, here are some other Google Sheets tutorials you may like.

Related:

• • How to Lock Cells in Google Sheets
  • How to Split Cell in Google Sheets
  • How to Unmerge Cells in Google Sheets
  • How to Group Rows & Columns In Google Sheets