How to Merge Cells and Combine Columns in Google Sheets
Let’s say you have a spreadsheet with three cells:
- A2 with “
Plymouth
“ - A3 with “
Road Runner
“ - A4 with “
1970
“
How do you join them together to get “Plymouth Road Runner - 1970
“? If you don’t know the answer, read on to find out. If you’re an experienced data enthusiast and know the solution, read on anyway. Below, you’ll find a detailed tutorial on how to merge cells in Google Sheets. Here we go!
Most users know about the Merge cells button on the spreadsheet toolbar.

It allows you to combine cells into one cell. The drawback of this option is that it DOES NOT merge the data. After the merge, you’ll get only the value in the left or top cell. So, our Plymouth task will fail as follows:

Nevertheless, you may find this option useful to visually tweak the table or dashboard you’re building in the spreadsheet.
If you’re looking for a solution to merge sheets and large data sets, check out our Tutorial on How to Combine Data from Multiple Sheets Into One Master Overview
How to merge cells and combine values in Google Sheets
There are a few functions that you can use to merge cells and concatenate values in one go. Let’s check out which of them can handle our Plymouth task.
Comparison table of functions to merge cells
Delimiter | Merging multiple cells | Merging date and time | Formula syntax | |
& | Yes | Yes | Yes* | =cell#1&"delimiter"&cell#2&... |
CONCAT | No | No | Yes* | =concat(cell#1, cell#2) |
CONCATENATE | Yes | Yes | Yes* | =concatenate(data_string#1,"delimiter",data_string#2…) |
JOIN | Yes | Yes | Yes | =join("delimiter",array#1,array#2…) |
TEXTJOIN | Yes | Yes | Yes | =textjoin("delimiter",ignore-empty,array#1,array#2…) |
SUBSTITUTE | Yes | Yes | Yes | =substitute("merge_pattern","text_to_search_for", "cell_to_replace_with", [occurrence_number]) |
*Only combined with the TEXT function.
Merge two cells (or more) with CONCAT
The CONCAT function can concatenate two cells or values only.
Syntax
=concat(cell#1, cell#2)
Plymouth task
We need to merge three cells within the Plymouth task, which means the CONCAT is a no go. If you use the following formula
=concat(A2,A3,A4)
it will return N/A error
Wrong number of arguments to CONCAT. Expected 2 arguments, but got 3 arguments.

However, there is a workaround:
=concat(A2, concat(A3,A4))
This formula will merge three values without a delimiter:

Put in a bit more effort, and here is a formula with delimiters
=concat(A2, concat(" ", concat(A3, concat(" ",A4))))

Merge several cells with &
The ampersand (&) is a concatenation operator to merge several cells. It also allows you to use different delimiters to separate the concatenated values.
Syntax
Without delimiters
=cell#1&cell#2&cell#3...
With delimiters
=cell#1&"delimiter"&cell#2&"delimiter"&cell#3&...
Plymouth task
Let’s apply the following formula to merge the cells for our Plymouth task:
=A2&" "&A3&" - "&A4

Ampersand did the job! However, this option might be not very convenient if you need to merge a really large number of cells. So, let’s keep going.
Merge multiple data strings with CONCATENATE
CONCATENATE is the function to combine multiple data strings into one. It’s the CONCAT’s elder brother (or sister) 🙂 We’ve blogged about the Google Sheets CONCATENATE function, so feel free to check it out!
Syntax
Without delimiters
=concatenate(data_range)
With delimiters
=concatenate(data_string#1,"delimiter",data_string#2,"delimiter",data_string#3...)
Plymouth task
Let’s use the CONCATENATE function to merge cells within the Plymouth task. We’ll apply two formulas, with and without delimiters:
=concatenate(A2," ",A3," - ",A4)
and
=concatenate(A2:A4)

Merge multiple cells with JOIN
JOIN is a function to concatenate values in one or several one-dimensional arrays (rows or columns) using a specified delimiter.
Syntax
=join("delimiter",array#1,array#2,array#3…)
Plymouth task
At first sight, JOIN does not let you apply different delimiters as CONCATENATE or & do:
=join(" ",A2:A4)
However, with a bit of creativity and you can handle this. Here is a JOIN formula to specify different delimiters:
=join("-", join(" ",A2:A3), A4)

Merge text strings with TEXTJOIN
TEXTJOIN is a function to concatenate text values in one or several one-dimensional arrays (rows or columns) using a specified delimiter.
Simply: JOIN for text values only.
Syntax
=textjoin("delimiter",ignore-empty,array#1,array#2,array#3…)
ignore-empty
– iffalse
, each empty cell within the array will be added as a delimiter; iftrue
, empty cells will be ignored.
Plymouth task
We have only text values in our Plymouth task, so the TEXTJOIN formula will handle it.
=textjoin(" ",false,A2:A4)
And here is a TEXTJOIN formula to use different delimiters for merged cells:
=textjoin(" - ",false, textjoin(" ",false,A2:A3),A4)

How ignore-empty
works
Let’s increase the array we use within the Plymouth task up to A20 cell and change the delimiter to “-
“. Check out the results returned with the ignore-empty
parameter set as either true
=textjoin("-",true,A2:A20)

or false
=textjoin("-",false,A2:A20)

BONUS: Merge cells with SUBSTITUTE
You may know SUBSTITUTE – a Google Sheets function to replace existing text strings with new ones. At the same time, it can serve as a way to merge cells (and even columns). Data analysts from Railsware actively use this approach in their workflow.
SUBSTITUTE formula syntax for merging cells:
=substitute("merge_pattern","text_to_search_for", "cell_to_replace_with", [occurrence_number])
merge_pattern
– specify the order of cells and delimiters to merge. For example,A2-A3:A4
.
Note: It’s not necessary to use cell indexes in the merge_pattern. You’re free to specify any text, which then will be replaced with a corresponding cell value.
text_to_search_for
– specify the text from themerge_pattern
to be replaced.
cell_to_replace_with
– specify the cell, which will replacetext_to_search_for
.
[occurrence_number]
– an optional parameter to specify the number oftext_to_search_for
instances to replace. By default, all instances will be replaced.
Plymouth task
Let’s modify the task to merge three cells (A2, A3, A4) using two different delimiters (" ", " - "
). So, the merge_pattern
is the following: A2 A3 - A4
. Now, we need to assign a cell index to each text_to_search_for
instance to implement the replacement. Here is how the formula will look:
=substitute(substitute(substitute( "{A2} {A3} - {A4}", "{A2}", A2), "{A3}", A3), "{A4}", A4)

The best part here is that you can use any text in the merge_pattern
that should be properly separated with the required delimiters. So, the following examples will work as well:
=substitute(substitute(substitute( "{Brand Name} {Model} - {Year}", "{Brand Name}", A2), "{Model}", A3), "{Year}", A4)

or
=substitute(substitute(substitute( "Any text you - want", "Any text", A2), "you", A3), "want", A4)

Merge cells with date and time
JOIN and TEXTJOIN are the best options to concatenate cells with date and time values. The SUBSTITUTE approach will do the job, as well. Check out the following formulas to merge two cells, A2 with date and A3 with time:
JOIN
=join(" ",A2:A3)
TEXTJOIN
=textjoin(" ",true,A2:A3)
SUBSTITUTE
substitute(substitute( "Date Time", "Date",A2), "Time",A3)

Other options – CONCAT, CONCATENATE and & – will return date-time values as simple numbers. For example, check out the CONCATENATE formula:
=concatenate(A2," ",A3)

This can be fixed if you embed the TEXT function in your CONCATENATE formula as follows:
=concatenate(text(A2,"yyyy-mm-dd")," ",text(A3,"h:mm:ss"))

The same workaround works for CONCAT and & (ampersand) formulas.
Merge data strings with a line break: CHAR(10)
All the functions above concatenate data in line. However, there is an option to make a line break between the data you merge. For this, you’ll need to add CHAR(10)
to your &/CONCAT/CONCATENATE/JOIN/TEXTJOIN/SUBSTITUTE formula.
CHAR is a function to insert special characters.
Syntax
=char(table_number)
table_number
– the special character number from the current Unicode table in decimal format.
Plymouth task
CHAR(10)
inserts a line break. Let’s embed this into our merging cells formulas:
&
=A2&char(10)&A3&char(10)&A4
CONCAT:
=concat(A2, concat(char(10), concat(A3, concat(char(10),A4)))
CONCATENATE:
=concatenate(A2,char(10),A3,char(10),A4)
JOIN:
=join(char(10),A2,A3,A4)
TEXTJOIN:
=textjoin(char(10),true,A2,A3,A4)
SUBSTITUTE:
=substitute(substitute(substitute(substitute( "brand-model-year", "brand", A2), "model", A3), "year", A4), "-",char(10))

How to combine columns in Google Sheets
None of the formulas above will merge columns. However, if you combine &, CONCAT or SUBSTITUTE with ARRAYFORMULA, you’ll get the result. Let’s see how this works in practice. But first, we’ll need to import some raw data to Google Sheets so that we can use it in our example. We’ll transfer data automatically with Coupler.io, a popular data integration tool. If you work with Google Sheets a lot, we recommend that you take a look at this solution as it’s very handy and makes working with spreadsheets a lot easier. It’s relatively new, but it already has 700 000+ users.
We will export data from Airtable, but there are 25+ other apps to choose from, for instance, QuickBooks, HubSpot, Pipedrive, Salesforce, and Mailchimp. See the complete list of the available integrations.

To export data, we create a Coupler.io account and select the source and destination for data transferring. In our example, that’s Airtable and Google Sheets. The next step is connecting the corresponding accounts and specifying what exactly we want to export and where.
And the final touch is setting a custom schedule for the updates. We’ve toggled on the Automatic data refresh feature, so now Coupler.io will automatically update Airtable data in Google Sheets according to our preferences. This means we will always have the latest data in this spreadsheet, which is handy for reporting, monitoring, and many other purposes.

In the end, we run the importer. Great! Now our data is transferred to Google Sheets, so we are ready to merge columns.
Merge columns horizontally
Our new task is to merge three columns: Date (A:A), New Orders (D:D), and Export Orders (L:L) using “ -
” as a delimiter.

ARRAYFORMULA + CONCAT
CONCAT can merge two cells only, and this pattern remains for columns as well. So, to concatenate three or more columns, we’ll need to use the work-around. Besides, don’t forget that CONCAT returns date-time values as simple numbers, so we’ll need to add TEXT to the formula:
=arrayformula( concat(text(A2:A,"yyyy-mm-dd"), concat(" - ", concat(D2:D, concat(" - ",L2:L)))))

ARRAYFORMULA + &
For ampersand (&) we don’t need to invent any workarounds. However, the TEXT function for date-time is still needed:
=arrayformula( text(A2:A,"yyyy-mm-dd") &" - " &D2:D &" - " &L2:L)

ARRAYFORMULA + SUBSTITUTE
Specify the merge_pattern
and add ARRAYFORMULA:
=arrayformula(substitute(substitute(substitute( "DATE - NEW ORDERS - EXPORT ORDERS" , "DATE",A2:A), "NEW ORDERS",D2:D), "EXPORT ORDERS",L2:L) )

CONCATENATE, JOIN or TEXTJOIN combined with ARRAYFORMULA don’t work. If you use either of these functions to merge columns, they will return all values from columns merged in a row.
Read more about Google Sheets ARRAYFORMULA in our blog post.
Merge columns vertically with an array and semicolon
Vertical merge means that the data from several columns will be appended to each other in a single column. Use an array ({}
) with a semicolon (;
) as a separator to merge column ranges.
As an example, we’ll use a dataset imported from Airtable to Google Sheets.

Syntax
={column_range#1;column_range#2;column_range#3…}
Formula example
Let’s merge the following data ranges: B1:B5, C1:C5, E1:E5
={B1:B5;C1:C5;E1:E5}

Tip: If you’re merging column ranges with duplicate values, use the UNIQUE function to remove duplicates. Here is the formula syntax:
=unique({column_range#1;column_range#2;column_range#3…})
Read our tutorial to learn more options to remove duplicates in Google Sheets.
To wrap up: Where will I need to merge cells in real life?
Frankly, for the use cases published on the Coupler.io blog, we’ve mostly needed to split cells in Google Sheets rather than merge them. If the data is being imported with Coupler.io, you can always select the append mode to concatenate data vertically.
However, you might need to combine cells or columns for your project or daily activities. Whatever your case is, we hope this article was useful to you. Good luck with your data!
Back to Blog