2

How To Combine Cells in Google Sheets

Posted by Jamie on July 23, 2018

Combining cell data is something that any serious spreadsheet user needs to know how to do, and Google Sheets is no exception. 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 concatenate 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 we will go over the process for combining cells in Google Sheets.

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. This article discusses combining cells; we’ll go over merging cells in another article.

Combine cells in Google Sheets

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

The main formula to use to combine numeric data and add them together 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.

There are two formulas you can use to combine string data. CONCAT is the simplest way to combine two cells together. Note that CONCAT can take only two arguments. 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 multiple 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”.

If you want to learn more about Google Sheets to build your mastery of this powerful free spreadsheet tool, TechJunkie has a lot more tutorials for you to view. Check them out and let us know what you think. If you want to see something in particular, email us and let us know!

2 thoughts on “How To Combine Cells in Google Sheets”

Yan moukoury says:
Hi there,

I hope all is well!

I am not sure if anyone would answer but i figure that at least I tried 🙂 I wanted to combine or merge to cell A and B the cells are pretty long about 5000 there isnt an example that shows how to combine or merge two whole cells those cless are Name plu companies …Any chance you can help

Reply
Nicolo says:
This is really helpful. However, here is a question. How do I concatenate two fields with a space in between when one of the fields is a date field and I want the date to show exactly as it displayed in another cell. Note that the date field is itself being populated and reformatted automatically from a cell on another tab in the same workbook. For example:
– On sheet #1 there is a date shown as 1/10/18 in column A
– On sheet #2 column A has the same date populated from sheet #1 and reformatted as 2018-01-18
– On sheet #1 column B has a description, e.g. John Smith
– On sheet #2 I want to concatenate the date from sheet #2 column A (reformatted date), a space, and then the contents of column B from sheet #1.
So basically sheet #2 column B should read: 2018-01-10 John Smith.
Thanks in advance.
Reply

Leave a Reply

Your email address will not be published. Required fields are marked *