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.

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 (2 Methods)

We have several examples of formulas that combine cells and columns and the methods you can use them remains 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

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 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.

Combine Cells without Seperator - Dataset

Below is the formula that will do this:

=A2&B2

Combine cells without separator formula

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:

=CONCATENATE(A2,B2)

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.

Combine Cells without Seperator - Dataset

Below is the formula that will do this:

=A2&"-"&B2

Combine cells with a separator formula

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

=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 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.

Data to combine with line breaks

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

Combine cells with line break using CHAR10

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:

=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.

Combining Text and Numbers - Dataset

Let me combine these two cells using a simple ampersand (with a dash as a separator) using the below formula:

=A2&" - "&B2

Number data format an issue when merged with text

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%")

Adding format to the number using the TEXT formula

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.

Dataset to combine text and date of birth

Below is the formula that will combine these two cells (with a dash as a separator).

A2&" - "&B2

Date format changes when combined with text

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

Text formula to format the date

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:

An example of using the unique function to combine columns

As you can see column C has the values of column 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 for the entire columns.

Read more: How to Use the UNIQUE Function.

The Google Sheets Combine Multiple Columns Into One FAQ

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 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 seperator, 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.

If You Found This Tutrialon How to Combine Cells in Google Sheets Here Are Some Other Google Sheets Tutorials You May Like:

Most Popular Posts

Sumit

Sumit

Google Sheets and Microsoft Excel Expert.

Leave a Comment