The easiest way to combine cells, rows, and columns in Google Sheets with custom separators is to use my free Google Sheets add-on, Paperpusher, which provides an easy way to combine rows and columns, split rows and columns, and correct other common OCR transcription and data entry errors and typos. However, if you’d rather not use Paperpusher, this series shows how to correct these errors using Google Sheets’ built-in functions.
Combining rows, columns, and cells in Google Sheets is actually an easy process. However, it can be frustrating before you figure it out.
Let’s say I have 2 cells, A1 and B1. They’re structured as follows:
|First Name||Last name|
I want to combine these two into a single cell, “Name”. In order to do that, I simply type in this formula in C1:
=A1 & ” ” & B1
and I get “Bob Belcher”. I can drag this formula down, if I have multiple first and last names, and it’ll automatically update for each cell.
To walk you through how this formula works, it takes the value from A1, combines it as text with a space ” “, then combines it as text with B1. It’s important that I use the ampersand &, instead of the plus sign +, so Google knows to treat the value as text (I’m not trying to find the sum of “Bob” and “Belcher”).
What if I want to put the last name first, so I can get “Belcher, Bob”? Easy. I just type in:
=B1 & “,” & ” ” & A1
Once again, I take the value from B1, combine it as text with a comma “,” and a space ” “, and then combine it with the value from A1.
This works with numbers as well. Say my data entry operator is confused, and he types in 1,500 as
|Number 1||Number 2|
I can just type in the following formula in C2
And Google will recognize it as 1,500. I can then do math exactly the same as I would do otherwise.
Reminder: If you want a quick and easy way to combine rows and columns, split rows and columns, and fix other common data entry and OCR transcription errors, check out my free Google Sheets add-on Paperpusher.