How To Combine Cells in Google Sheets (2021)

Google Sheets is the powerful free spreadsheet solution that Google rolled out as part of Google Docs in 2005. Sheets makes sharing spreadsheet data among teams extremely easy with its cloud-based storage and straightforward workgroup features. Although Sheets does not have the full power of a complete spreadsheet solution like Excel, it is an excellent tool for basic (and even some kinds of advanced) spreadsheet analysis. One feature that Sheets does exceptionally well with is managing user data, for example, combining cells in a spreadsheet.

Combining Cells

Combining cell data is something that any serious spreadsheet user needs to know how to do; Google Sheets makes it a relatively painless process. Data sources almost always require editing and tidying up to be useful, and this very often requires combining or concatenating cells. For example, if you have a spreadsheet where first names and last names are in different columns, you may want a column which has each person’s full name. You can use the concatenate command with the first two columns to combine their information into a third column. All you need to combine cells are two (or more) cells containing data and a destination cell in which to display the concatenated data. In this article you will learn the process for combining cells in Google Sheets.

Why am I using a big word like “concatenate” instead of something simple like “combine”? Well, the commands for combining cells in Sheets (and Excel for that matter) use the word “concatenate” a lot and we might as well get used to it!

Note that combining cells and merging cells, although they sound very similar in plain English, are actually two entirely different operations in Google Sheets and other spreadsheets. Merging cells means combining two or more cells into one and eliminating the previous cells; combining cells means taking the contents of both and putting them somewhere else. This article discusses combining cells.

What does the data look like?

The first thing we need to think about is whether we are looking at string data (“My name is David”), numerical data (32), or a combination of the two (“David has 32 apples”), and what we want the concatenated data to look like. For example, we might have “John” in one cell, “Smith” in a second cell, and desire an output of “John Smith”. On the other hand we may have a value of 100 in one cell, 300 in another cell, and we want an output of 400. Or it’s possible that we have “John” in one cell, 200 in another cell, and we want the output to be “John200” or “John 200”. There are different formulas to use to reach of these different types of results.

Numerical Data

For purely numeric data, the function for adding them up is SUM. To use SUM:

1. Open your Google Sheet.
2. Find the cells you want to combine and note their coordinates – in this example, A1 and A2.
3. In the cell in which you want to display the combined data, type ‘=sum(A1, A2)’. You can also use a range in the sum formula, i.e., ‘=sum(A1:A2)’.

You should now see the sum of A1 and A2 in the destination cell. So if A1 contained 100 and A2 contained 50, the destination cell should contain 150. Note that you can ask SUM to give you a total over a range that includes string data, but that string data will be ignored. If cell A2 in this example contained “50” rather than 50, the total would be 100, not 150.

String Data

There are two formulas you can use to combine string data. CONCAT is the simplest way to combine two cells together. However, CONCAT has an important limitation: it can take only two arguments. That is, you can only put two things together with CONCAT. To use CONCAT:

1. Open your Google Sheet.
2. Find the cells you want to combine and note their coordinates – in this example, A1 and A2.
3. In the cell in which you want to display the combined data, type ‘=concat(A1, A2)’.

You should now see the combination of A1 and A2 in the destination cell. If A1 contained “rocket” and A2 contained “ninja”, the destination cell should contain “rocketninja”.

But what if you want the destination cell to contain “rocket ninja”, or what if you have five different cells whose text you want to combine? In that case you need to use the more powerful CONCATENATE command. To use CONCATENATE:

1. Open your Google Sheet.
2. Find the cells you want to combine and note their coordinates – in this example, A1 and A2.
3. In the cell in which you want to display the combined data, type ‘=concatenate(A1, ” “, A2)’.

You should now see the combination of A1, a space, and A2 in the destination cell. If A1 contained “rocket” and A2 contained “ninja”, the destination cell should contain “rocket ninja”. Note that you can specify as many cells, string constants, or ranges in CONCATENATE as you wish; ‘=concatenate(A1, ” “, A2, “this is a silly example”, A1:B2999)’ is a perfectly valid formula.

You should be aware that CONCAT and CONCATENATE will work perfectly well with numeric data, but it will treat that data as a string, not as a number. CONCAT(100,200) and CONCAT(“100″,”200”) will both output “100200”, not 300 or “300”.

In addition to CONCAT and CONCATENATE, Sheets can use the ampersand (&) operator as a combining tool. You can use & with numbers and text indiscriminately. However, it will assume that any number is actually text; “Monkey” & 100 & “Shines” comes out to “Monkey100Shines”.

That’s a basic introduction to combining cell contents in Sheets. Want to learn more about using Sheets? TechJunkie has plenty of Sheets tutorials, including these: how to hide cells in Sheets, how to lock cells, how to find the slope of a line in Sheets, how to compare columns, how to remove duplicate rows, and many more.

Have any suggestions for combining cells in Sheets? Share them with us below in the comments area!