Google Sheets CONCATENATE Function to Combine Data from Multiple Cells Together
Cell A1 contains “Jane“, cell B1 contains “Pontiac“, and cell C1 contains “1970“. We need to combine the values from these cells into one to return “Jane drives Pontiac since 1970“.
In Google Sheets, there is the CONCATENATE function that you can use to combine multiple data strings into one. It did the job and, in this article, we’ll show how you can use this function for other advanced cases you may have.
Google Sheets function to CONCATENATE multiple strings
Syntax of Google Sheets CONCATENATE with separator
=CONCATENATE(data_string#1,"delimiter",data_string#2,"delimiter",data_string#3...)data_string– a reference to a celldelimiter(aka separator) can be a symbol or string that separates the data strings
Formula example of Google Sheets CONCATENATE with delimiter
Here is the CONCATENATE formula for the use case we introduced above:
=CONCATENATE(A1," drives ",B1," since ",C1)
A1,B1, andC1are data strings" drives "and" since "are delimiters
You can also use CONCATENATE without delimiters.
Syntax of CONCATENATE Google Sheets without a delimiter
In this case, you just need to specify the data range, the values from which will be merged.
=CONCATENATE(data_range)Formula example of Google Sheets CONCATENATE range
And here is the formula for merging values without delimiters:
=CONCATENATE(A1:C1)
The CONCATENATE Google Sheets function is easy to understand and use, but don’t close this blog post just yet. Below you will find examples and explanations of how to use CONCATENATE for more complex tasks.
How to CONCATENATE in Google Sheets – Advanced Cases
For the examples below, we will be using a data set exported from BigQuery to Google Sheets using Coupler.io.
Coupler.io is a solution for exporting data to Google Sheets and BigQuery from different apps (Airtable, HubSpot, Shopify, etc.) and sources (CSV, Excel, Google Sheets).
If you need to automate exports of data from your CRM, Ad platform, or some other source, try out Coupler.io for free.
The data set contains six columns:
- first_name (
A1:A) – textual values - last_name (
B1:B) – textual values - age (
C1:C) – numeric values - car (
D1:D) – textual values - first_registration_date (
E1:E) – date values - date_of_sale (
F1:F) – date values
Let’s play with them to check out different cases to concatenate data.
Google Sheets CONCATENATE cells
Let’s start with the basics: how to concatenate cells in Google Sheets. We need to combine the first name and last name strings to get a full name string – for example “Deina Andreolli“. Here is the CONCATENATE formula:
=CONCATENATE(A2,B2)
We’ve got “DeinaAndreolli“… not quite what we needed. So, let’s amend the formula a bit.
Google Sheets CONCATENATE with space
To concatenate strings with space in Google Sheets, you need to use a space delimiter: " ". So, in our case, the CONCATENATE Google Sheets formula will look like this:
=CONCATENATE(A2," ",B2)
Google Sheets CONCATENATE multiple cells (more than 2)
Let’s move further and concatenate a few more cells into one string. For example, we need to obtain the phrase “Deina Andreolli drives Toyota“. For this, we’ll combine three cells using two delimiters. Here is the CONCATENATE formula:
=CONCATENATE(A2," ",B2, " drives ", D2)
Pay attention to the spaces that we added in the delimiter " drives ".
Google Sheets CONCATENATE new line
If you want to concatenate each data string from a new line, you’ll need to add &char(10) to your CONCATENATE Google Sheets formula as follows:
=CONCATENATE(A2 &char(10),B2 &char(10), "drives" &char(10), D2)
Accordingly, you can add &char(10) as a separate data string if you want to place a line between data strings:
=CONCATENATE(A2 &char(10),char(10),B2 &char(10), char(10),"drives" &char(10), char(10), D2)
Google Sheets CONCATENATE string and number
Let’s enhance our target string with a number value like this: “Deina Andreolli aged 67 drives Toyota“. For this, we’ll add another delimiter, " aged ", and a reference to a cell with a numeric value. Here is how the CONCATENATE formula will look:
=CONCATENATE(A2," ",B2, " aged ",C2," drives ", D2)
Don’t you think that the phrase would sound better if we add some commas to it – “Deina Andreolli, aged 67, drives Toyota“? Let’s do this.
Google Sheets CONCATENATE with comma
You can concatenate with commas in Google Sheets. For this, you need to add commas to your CONCATENATE formula as delimiters. In our case, we already have the delimiters and there is no need to add separate ones. All we need to do is add commas to two delimiters: " aged " and " drives " as follows:
=CONCATENATE(A2," ",B2, ", aged ",C2,", drives ", D2)
Google Sheets CONCATENATE date strings
Hmm… and what about some date values in our concatenated string? Let’s try to return the following wording:
“Deina Andreolli, aged 67, drives Toyota since 4/14/2017“
If you simply add a cell with a date to your CONCATENATE formula, the date will look like a string – for example:
=CONCATENATE(A2," ",B2, ", aged ",C2,", drives ", D2, " since ", E2)
To return a date value in a required format, use the TEXT function in the CONCATENATE formula like this:
=CONCATENATE(A2," ",B2, ", aged ",C2,", drives ", D2, " since ", TEXT(E2,"mm/dd/yyyy"))
With the TEXT function, you can use the format you want such as mm/yyyy, mmmm, and so on. In a similar way, you can use other date functions such as YEAR, MONTH, DAY, and so on. Read our blog post to learn more about Google Sheets date format.
Now we have the target phrase concatenated from the values in a row. Let’s do the same for the rest of the rows.
How to concatenate columns in Google Sheets
The first idea that comes to mind is just to drag the CONCATENATE formula down.
This will copy the formula to the below cells and we’ll get what we want. However, this way is not convenient if:
- You have too many rows to cover
- You want to have new records concatenated automatically
Can we use ARRAYFORMULA for this?
Google Sheets concatenate columns with space using ARRAYFORMULA
Unfortunately, CONCATENATE nested with ARRAYFORMULA will return all values from columns merged in a row.
But you can use an ampersand (&) instead of CONCATENATE and nest it within ARRAYFORMULA. The syntax will be a bit different:
=ARRAYFORMULA(data_string#1&"delimiter"&data_string#2&"delimiter"&data_string#3…)This will concatenate columns horizontally. Here is what the formula will look like:
=ARRAYFORMULA(A2:A&" "&B2:B&", aged "&C2:C&", drives "&D2:D&" since "&TEXT(E2:E,"mm/dd/yyyy"))
Read more about CONCATENATE alternatives in our blog post, How to Merge Cells and Combine Columns in Google Sheets.
How to concatenate columns vertically in Google Sheets without formulas
In most cases, you need to concatenate ranges in Google Sheets vertically from two or more sheets with the same structure of columns – for example:
The quickest and easiest way to do this is via Coupler.io. You need to specify the sheets to be concatenated vertically, as well as the range common to these sheets. Coupler.io will exclude the title row, as well as duplicates, automatically. Here is how it works:
You need to sign in to Coupler.io, click Add importer, and select Google Sheets as the source and destination apps. Then complete the following steps:
Set up Source (Google Sheets)
- Connect your Google account or choose one from the list if you’ve used Coupler.io before.
- Choose the spreadsheet and the sheets the columns from which you want to concatenate.
- Optionally, you can specify the range to concatenate in the selected sheets.
Note: If you want to concatenate columns from the sheets that have a patterned name like we have, then you can use the following pattern:
{sheet-name}.+
+should cover the variable part of the sheet name
In our case, this will look like this:
Invoices.+
Set up Destination (Google Sheets)
The flow is the same as for the source, but now you need to choose the spreadsheet and the sheet to export the concatenated columns to. Optionally, you can specify the first cell to load the concatenated data to, as well as the import mode (replace or append).
Set up Schedule
If you want to concatenate columns from multiple sheets on a schedule, enable the automatic data refresh and customize the frequency.
When all the settings are configured, click Save and Run. And there you go! Here is what the concatenated columns will look like:
Bonus stage
Google Sheets concatenate text and formula
The idea of concatenating a formula and a text string is that you can add some text to the result of a calculation. For example, you may need to calculate the total distance you covered for the year and attach it with ‘miles’ or ‘kilometers’.
In this case, the formula acts like a regular string or cell reference in your concatenation formula. Besides, you can do this using either the CONCATENATE function or ampersand.
Combine text and formula with the CONCATENATE function in Google Sheets
Here is the formula syntax to concatenate formula and text in Google Sheets:
=CONCATENATE(formula,"string")For example:
=concatenate(sum(B2:B13)," miles")
However, you can change the order of strings and formulas in your CONCATENATE statement, for example as follows:
=concatenate("In total, I covered ",sum(B2:B13)," miles")
Combine text and formula with an ampersand in Google Sheets
Let’s do the same using ampersand. The formula syntax will look like this:
=formula&"string"Here is what it looks like in our example 1:
=sum(B2:B13)&" miles"
and example 2:
="In total, I covered "&sum(B2:B13)&" miles"
Feel free to choose the option you like most and go ahead!
Google Sheets CONCATENATE + IF
Let’s add some conditions to our CONCATENATE formula. For example, we don’t like MINIs… and want to highlight this. So, we add the condition that will return “We don’t like MINIs” for any MINI data string in D2:D. Here is how the formula will look:
=IF(D2:D="MINI","We don’t like MINIs", CONCATENATE(A2," ",B2, ", aged ",C2,", drives ", D2, " since ", TEXT(E2,"mm/dd/yyyy")))
This condition will work much better if used in ARRAYFORMULA (but without CONCATENATE) as follows:
=arrayformula(IF(D2:D="MINI","We don’t like MINIs", (A2:A&" "&B2:B&", aged "&C2:C&", drives "&D2:D&" since "&TEXT(E2:E,"mm/dd/yyyy"))))
Google Sheets concatenate with QUERY
Unfortunately, you can’t use the CONCATENATE function in Google Sheets QUERY. Fortunately, there is a workaround.
Let’s say you need to query some data set and concatenate the queried data into one cell without any delimiters. There is an advanced TRANSPOSE formula, which you can use right away:
=TRANSPOSE(QUERY(TRANSPOSE(UNIQUE(QUERY({insert-your-query}))),,COLUMNS(UNIQUE(QUERY({insert-your-query})))))Check out how it works in the example. We need to query three columns (first_name, second_name, and car) from our data set based on one criteria: we want to know which users drive a Mitsubishi. Our QUERY formula will look as follows:
=query('Query from BigQuery'!A:D,"select A,B,D where D = 'Mitsubishi'")
If we insert this query into the TRANSPOSE formula above, we’ll get the following:
=transpose(
query(
transpose(
UNIQUE(
query('Query from BigQuery'!A2:D,"select A,B,D where D ='Mitsubishi'")
)
),,
COLUMNS(
UNIQUE(
query('Query from BigQuery'!A:D,"select A,B,D where D='Mitsubishi'")
)
)
)
)
What else can you use the CONCATENATE function in Google Sheets for?
As a final word, we decided to introduce another interesting case for using the CONCATENATE function. If you need to encode your string into BASE64 format, there is an advanced formula that includes CONCATENATE, ARRAYFORMULA, JOIN, SPLIT, and other Google Sheets functions. Here it is:
=CONCATENATE(JOIN("",ARRAYFORMULA(SWITCH(BIN2DEC(TEXT(SPLIT(REGEXREPLACE(CONCATENATE(JOIN("",ARRAYFORMULA(BASE(UNICODE(SPLIT(REGEXREPLACE(REGEXREPLACE(CELL&"","(?s)(.{1})","$1"&CHAR(127)),"'","''"),CHAR(127))),2,8))),REPT("0",(FLOOR((LEN(JOIN("",ARRAYFORMULA(BASE(UNICODE(SPLIT(REGEXREPLACE(REGEXREPLACE(CELL&"","(?s)(.{1})","$1"&CHAR(127)),"'","''"),CHAR(127))),2,8))))+(6-1))/6)*6)-LEN(JOIN("",ARRAYFORMULA(BASE(UNICODE(SPLIT(REGEXREPLACE(REGEXREPLACE(CELL&"","(?s)(.{1})","$1"&CHAR(127)),"'","''"),CHAR(127))),2,8)))))),"(.{6})","$1/"),"/"),"000000")),0,"A",1,"B",2,"C",3,"D",4,"E",5,"F",6,"G",7,"H",8,"I",9,"J",10,"K",11,"L",12,"M",13,"N",14,"O",15,"P",16,"Q",17,"R",18,"S",19,"T",20,"U",21,"V",22,"W",23,"X",24,"Y",25,"Z",26,"a",27,"b",28,"c",29,"d",30,"e",31,"f",32,"g",33,"h",34,"i",35,"j",36,"k",37,"l",38,"m",39,"n",40,"o",41,"p",42,"q",43,"r",44,"s",45,"t",46,"u",47,"v",48,"w",49,"x",50,"y",51,"z",52,"0",53,"1",54,"2",55,"3",56,"4",57,"5",58,"6",59,"7",60,"8",61,"9",62,"-",63,"_",64,"="))),REPT("=",(FLOOR((LEN(JOIN("",ARRAYFORMULA(SWITCH(BIN2DEC(TEXT(SPLIT(REGEXREPLACE(CONCATENATE(JOIN("",ARRAYFORMULA(BASE(UNICODE(SPLIT(REGEXREPLACE(REGEXREPLACE(CELL&"","(?s)(.{1})","$1"&CHAR(127)),"'","''"),CHAR(127))),2,8))),REPT("0",(FLOOR((LEN(JOIN("",ARRAYFORMULA(BASE(UNICODE(SPLIT(REGEXREPLACE(REGEXREPLACE(CELL&"","(?s)(.{1})","$1"&CHAR(127)),"'","''"),CHAR(127))),2,8))))+(6-1))/6)*6)-LEN(JOIN("",ARRAYFORMULA(BASE(UNICODE(SPLIT(REGEXREPLACE(REGEXREPLACE(CELL&"","(?s)(.{1})","$1"&CHAR(127)),"'","''"),CHAR(127))),2,8)))))),"(.{6})","$1/"),"/"),"000000")),0,"A",1,"B",2,"C",3,"D",4,"E",5,"F",6,"G",7,"H",8,"I",9,"J",10,"K",11,"L",12,"M",13,"N",14,"O",15,"P",16,"Q",17,"R",18,"S",19,"T",20,"U",21,"V",22,"W",23,"X",24,"Y",25,"Z",26,"a",27,"b",28,"c",29,"d",30,"e",31,"f",32,"g",33,"h",34,"i",35,"j",36,"k",37,"l",38,"m",39,"n",40,"o",41,"p",42,"q",43,"r",44,"s",45,"t",46,"u",47,"v",48,"w",49,"x",50,"y",51,"z",52,"0",53,"1",54,"2",55,"3",56,"4",57,"5",58,"6",59,"7",60,"8",61,"9",62,"-",63,"_",64,"="))))+(4-1))/4)*4)-LEN(JOIN("",ARRAYFORMULA(SWITCH(BIN2DEC(TEXT(SPLIT(REGEXREPLACE(CONCATENATE(JOIN("",ARRAYFORMULA(BASE(UNICODE(SPLIT(REGEXREPLACE(REGEXREPLACE(CELL&"","(?s)(.{1})","$1"&CHAR(127)),"'","''"),CHAR(127))),2,8))),REPT("0",(FLOOR((LEN(JOIN("",ARRAYFORMULA(BASE(UNICODE(SPLIT(REGEXREPLACE(REGEXREPLACE(CELL&"","(?s)(.{1})","$1"&CHAR(127)),"'","''"),CHAR(127))),2,8))))+(6-1))/6)*6)-LEN(JOIN("",ARRAYFORMULA(BASE(UNICODE(SPLIT(REGEXREPLACE(REGEXREPLACE(CELL&"","(?s)(.{1})","$1"&CHAR(127)),"'","''"),CHAR(127))),2,8)))))),"(.{6})","$1/"),"/"),"000000")),0,"A",1,"B",2,"C",3,"D",4,"E",5,"F",6,"G",7,"H",8,"I",9,"J",10,"K",11,"L",12,"M",13,"N",14,"O",15,"P",16,"Q",17,"R",18,"S",19,"T",20,"U",21,"V",22,"W",23,"X",24,"Y",25,"Z",26,"a",27,"b",28,"c",29,"d",30,"e",31,"f",32,"g",33,"h",34,"i",35,"j",36,"k",37,"l",38,"m",39,"n",40,"o",41,"p",42,"q",43,"r",44,"s",45,"t",46,"u",47,"v",48,"w",49,"x",50,"y",51,"z",52,"0",53,"1",54,"2",55,"3",56,"4",57,"5",58,"6",59,"7",60,"8",61,"9",62,"-",63,"_",64,"="))))))
Just reference your cell with the string instead of CELL, and hit enter. Good luck with your data!
