CONCATENATE function: formulas to merge cells in Google Sheets

"Concatenate" usually means to link something together in a series or a chain. This operation is used in Google spreadsheets whenever you need to join records from multiple 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 records from multiple cells 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.

Google Sheets offers several tools for this task.

Google Sheets CONCAT function

The CONCAT function is a simplified version of CONCATENATE:

=CONCAT(value1, value2)

To join cells, it requires:

  • value1 - a record to which value2 should be appended.
  • value2 - the value to join.

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

Put the data directly into the formula to concatenate.

In reality, your data is most likely already in cells. You can refer 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)

The result of the Google Sheets CONCAT formula.

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:

  • it joins only 2 values at a time.
  • it cannot combine data ranges, only single cells. If you try to merge multiple cells, you'll either get an error or only first two values will be joined, like this:

    =CONCAT(A2:A11,B2:B11)

    Try to join ranges with CONCAT.

CONCAT alternative: concatenation operator ampersand (&)

Google Sheets contains different operators for various purposes. 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

Use ampersand to concatenate values.

But little do you know that this сoncatenation operator is more flexible than CONCAT. Here's what it can do:

  1. Merge more than two values at a time:

    =A2&B2&C2

    Combine multiple records with the ampersand.

  2. Not only merge several cells in Google Sheets, but also separate them with various characters:

    =A2&" "&B2&"; "&C2

    Merge cells in Google Sheets using delimiters.

If you still don't get the desired result with these options, there's one more function you should try.

How to use CONCATENATE in Google Sheets

I believe Google Sheets CONCATENATE function is the first one to use when it comes to appending several records together.

CONCATENATE text and numbers in Google Sheets

The formula pattern consists of the following:

=CONCATENATE(string1, [string2, ...])
  • string1 is the first string you want to append other values to. This argument is required.
  • string2, … stands for all other strings you may want to append to the initial string. This argument is optional.
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)

Google Sheets CONCATENATE on real data.

You can immediately notice the first advantage of Google Sheets CONCATENATE: it can easily join over two cells with both text and numbers.

Google spreadsheets: concatenate with separators

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 put the formula as is, it will concatenate text and glue everything together: BonnieJacksonCA, BonnieJacksonIN, etc. The good news is that Google Sheets CONCATENATE takes characters as arguments, too.

Thus, to divide combined strings with separators, simply mention them in the formula in double-quotes:

=CONCATENATE(A2," ",B2,", ",C2)

Here I want to separate A2 from B2 with space and B2 from C2 with a comma and space:

Google spreadsheet - concatenate with separators.

You are free to use almost any character in the function like this, but a line break requires a different approach.

Concatenate text with a line break

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. And you probably know that all characters have a place in the Unicode table. So simply 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 concatenation formula to have records separated by line breaks:

=CONCATENATE(A2,CHAR(10),B2,CHAR(10),C2,CHAR(10),D2)

Formula to join records with the line break.

Google Sheets: combine date and time

If you try to combine date and time using one of the methods above, they won't work. Your spreadsheet will return records as simple numbers:

Combine date and time in Google Sheets.

To merge these cells correctly in Google Sheets, you need to use the TEXT function:

=TEXT(number, format)
  • where number is any number, date, or time you'd like to get in the desired format
  • and format is the pattern you'd like to apply.
Tip. In my example, I'm going to use references to cells with dates and times, though you are free to use the records themselves directly in the formula or even the function such as DATE or TIME.
  1. I use the first TEXT formula in order to change the date format from 7/9/2019 to 9 Jul 2019:

    =TEXT(B2,"D MMM YYYY")

  2. The second TEXT returns the time:

    =TEXT(C2,"HH:MM:SS")

  3. Using these in CONCATENATE, Google Sheets lets me combine date and time in the desired format with other characters or text:

    =CONCATENATE(TEXT(B2,"D MMM YYYY"),", ",TEXT(C2,"HH:MM:SS"))

Combine date and time with other chars and text in Google Sheets.

Combine columns in Google Sheets

With slight adjustments, all the ways I mentioned are capable of merging columns in Google Sheets.

Example 1. Google Sheets CONCAT

To merge whole columns in Google Sheets with CONCAT, select the entire range that should contain the result (C2:C11 in my case) and wrap the function in ARRAYFORMULA:

=ARRAYFORMULA(CONCAT(A2:A11,B2:B11))

Array formula to combine two columns in Google Sheets.

Note. If you'd rather use the CONCATENATE function, it will join all records within one cell since it easily merges multiple cells and data ranges.

Example 2. Concatenation operator

Create array formulas to combine entire columns with the ampersand and add separators at the same time:

=ARRAYFORMULA(A2:A11&" "&B2:B11&"; "&C2:C11)

The ampersand in the array formula to combine cells in Google Sheets.

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.

Example 3. Google Sheets QUERY

QUERY is also suited to merge several columns in Google Sheets. Have a look:

=TRANSPOSE(QUERY(TRANSPOSE(A2:D10),,9^9))

How to combine cells in Google Sheets with QUERY.

You may think this strange formula is beyond your grasp, but let me lay its all pieces out for you:

  1. =TRANSPOSE(A2:D10) turns the rows of data into columns.
  2. =QUERY(TRANSPOSE(A2:D10),,9^9) merges records in each column to the top cells.
    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 it includes all possible cells in the spreadsheet (remember the limit for 5M cells?) and is easy to remember. :)
  3. =TRANSPOSE(QUERY(TRANSPOSE(A2:D10),,9^9)) takes that header row from QUERY and turns it into a column like the one I've got.

The perks of the Google Sheets QUERY for combining columns lay in these facts:

  • you don't need to select the entire column as you do for array formulas
  • you don't need to mention each column in the formula unless they are non-adjacent. In this case, here's how the formula will look:

    =TRANSPOSE(QUERY(TRANSPOSE({A2:A10,C2:C10,E2:E10,G2:G10}),,9^9))

Concatenate and add text by position

You already know that you can add missing text, numbers, and characters to your strings using the CONCATENATE function.

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 as they are or using 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 to cells based on the place you specify, no formulas needed. Let me show you how it works.

In the previous example QUERY combined 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:

Add text by position with Ablebits add-on.

Split cells in Google Sheets

When the concatenation takes place, it is often required to divide merged cells back. There are three ways to do that in Google Sheets:

  1. Build Google Sheets SPLIT function.
  2. Use the standard spreadsheet instrument - Split text to columns.
  3. Or try out the enhanced version of the built-in tool - Split text to columns for Google Sheets:

    Ablebits add-on to split cells to columns in Google Sheets.

    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 or separate first and last names.

How to merge cells in Google Sheets without formulas

If mastering different formulas is not a 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.

  1. You can choose to combine columns in Google Sheets, separate them with commas and spaces, and place the result to the right of the original records:

    Merge values in each selected row.

  2. Or merge rows in Google Sheets, divide records with line breaks, and clear the contents of selected cells:

    Merge values in each selected column.

  3. Or select the range and have all cells merged into one altogether:

    Merge cells in Google Sheets without losing the data.

    If you've got interested in the tool, you can look through everything it does on this special page.

  4. There's one more utility we offer for concatenating in Google Sheets - Combine Duplicate Rows. On one hand, it merges cells from different rows that have the same key record. On the other hand, it concatenates numbers that are scattered over your table but still belong to the same record:

    How to combine cells from duplicate rows in Google Sheets.

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

You may also be interested in:

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!