by Natalia Sharashova, updated on
"Concatenate" usually means to link something together in a series or a chain. This operation is used whenever you need to join text from multiple Google Sheets cells. This article collects the most popular and easy solutions to help you solve the concatenation puzzle.
However big your dataset is, you may come across the task of combining multiple cells in Google Sheets together. And I have no doubt you will want to not only keep all values from losing, but also add some commas, spaces, or other characters, or even separate those records with other text.
Well, spreadsheets offer several tools for this task.
The CONCAT function is a simplified version of Google Sheets CONCATENATE:
To join cells with this function, you need to list the required values:
To get one string from 2 text or numeric units, the formula will look like below, with each record in double-quotes:
=CONCAT("2019:","The Lion King")
In reality, your data is most likely already in cells. You can refer to those cells directly instead of putting down each number or text as an argument. So the real-data formula will be like this:
=CONCAT(A2,B2)
Tip. To copy your formula to the whole column, select the cell with the formula and double-click the little square at the bottom right corner of the cell. The entire column will be automatically filled with the formula, till the very end of the table.
As you can see, the function is super simple, but it has major weak points:
=CONCAT(A2:A11,B2:B11)
There are lots of different operators for various purposes in formulas. Concatenation is not an exception. Using an ampersand character (&) in formulas instead of the CONCAT function will provide you with the same result:
=A2&B2
But little do you know that this concatenation operator is more flexible. Here's what it can do:
=A2&B2&C2
=A2&" "&B2&"; "&C2
If you still don't get the desired result with these options, there's one more function to try.
I believe the Google Sheets CONCATENATE function is the first one to use when it comes to appending several records together.
The formula pattern consists of the following arguments:
Note. The outcome record will consist of the strings in the order of their appearance in the formula.
If I adapt the formula to my data, I will get this:
=CONCATENATE(A2,B2,C2)
Or, since the function accepts ranges:
=CONCATENATE(A2:D2)
You can immediately notice the first advantage of Google Sheets CONCATENATE: it easily joins over two cells with both text and numbers.
Combining cells in Google Sheets is half the work. But to make the result look pretty and readable, you should add some extra characters.
If you keep the formula as is, it will just glue everything together: BonnieJacksonCA, BonnieJacksonIN, etc. But Google Sheets CONCATENATE takes characters as arguments, too.
Thus, to add some separators for readability, mention them in double-quotes in the formula:
=CONCATENATE(A2," ",B2,", ",C2)
Here I want to concatenate A2 & B2 with space and separate B2 from C2 with a comma and space:
You are free to use almost any character in the function like this, yet a line break requires a different approach.
Tip. In case there are empty cells in some of the columns you're merging, there's one more function you may be interested in. TEXTJOIN not simply merges cells in Google Sheets but ignores blanks:
=TEXTJOIN(" ",TRUE,A2:C2)
Here's how it works:
While it's obvious how to enter most delimiters to the function, you cannot type a line break the same way there. But luckily Google lets you play many different cards.
There's a function that helps to get special characters – it's called CHAR. You see, each character has a place in the Unicode table. You just need to feed the ordinal number of the character from that table to the function and the latter will return the character itself.
Here's a formula to get the line break:
=CHAR(10)
Add it to the formula to concatenate with the line break in Google Sheets:
=CONCATENATE(A2,CHAR(10),B2,CHAR(10),C2,CHAR(10),D2)
If you try to combine date and time in Google Sheets using one of the methods above, it won't work. Your spreadsheet will return numbers:
To combine date and time in Google Sheets correctly, use the TEXT function:
Tip. In my example, I'm going to reference cells with dates and times, but you are free to use date/time units or even such functions as DATE or TIME directly in the formula.
=TEXT(B2,"D MMM YYYY")
=TEXT(C2,"HH:MM:SS")
=CONCATENATE(TEXT(B2,"D MMM YYYY"),", ",TEXT(C2,"HH:MM:SS"))
With slight adjustments, all the ways I mentioned are capable of merging columns in Google Sheets.
To merge whole columns in Google Sheets with CONCAT, select the entire range that should contain the result (C2:C11 in my case) and enter your formula wrapping it in ARRAYFORMULA:
=ARRAYFORMULA(CONCAT(A2:A11,B2:B11))
Note. You could use the CONCATENATE function, but it will join all records within one cell since it easily merges multiple cells and data ranges.
Create array formulas to combine columns in Google Sheets using the ampersand and add separators at the same time:
=ARRAYFORMULA(A2:A11&" "&B2:B11&"; "&C2:C11)
This looks good, but I have to point out some major cons.
If you have too many columns, enumerating them all can become a pain in the neck, especially if you accidentally skip/duplicate/mix up any characters.
Also, if you decide to add more columns to the formula later, you'll have to edit each and every existing range in the formula manually.
The next example solves these problems.
Google Sheets QUERY function is also suited to merge several columns in Google Sheets. Have a look:
=TRANSPOSE(QUERY(TRANSPOSE(A2:D10),,9^9))
You may think this strange formula is beyond your grasp, but let me lay its all pieces out for you:
Tip. When I put 9^9 into the formula, I make sure all rows from all columns will be pulled into the first row as if they were headers. It is 9^9 since this expression includes all possible cells in the spreadsheet (remember the limit for 10M cells?) and is easy to remember. :)
Here are the perks of merging columns in Google Sheets using QUERY:
=TRANSPOSE(QUERY(TRANSPOSE({A2:A10,C2:C10,E2:E10,G2:G10}),,9^9))
You already know that you can add missing text, numbers, and characters to your strings using the CONCATENATE function.
Tip. See more formulas on that in this tutorial.
But if there are too many records to join, any extra characters can extend your formula way beyond what you have planned. In cases like this, it's better to merge cells in Google Sheets as they are or use simple delimiters like space and add the text after that. A special tool of ours will help you.
Add text by position inserts any chars and strings by the position you specify, no formulas are needed. Let me show you how it works.
In the previous example QUERY joined names and phone numbers for me. But I'd like to add country abbreviations: (USA/CA) before phone numbers that start with +1 and UK before +44:
If you combine cells in Google Sheets, chances are you'll need to split them back at some point. There are three ways to do that:
It lets you split cells by any delimiter or even sets of separators, treating them as one and including conjunctions if necessary. It also offers the option to split cells in Google Sheets by position.
Tip. There's an option to extract data from Google Sheets cells rather than split the contents.
If mastering different formulas is not part of your plan, you will benefit from our Merge Values add-on. The add-on quickly joins records in rows, columns, or the entire range of cells. Its options are crystal clear, and all you are to do is select the range and decide on how the result should look.
If you've got interested in the tool, you can look through everything it does on this special page or in this short video tutorial:
Learn how to use Combine Duplicate Rows in this video:
I hope by now you've decided which of the ways suits your case best. If you have any other methods in mind, please do share with us in the comments section below :)
Table of contents
