Back to Blog

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“. 

Google Sheets CONCATENATE Function to Combine Data From Multiple Cells Together

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 cell
  • delimiter (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, and C1 are data strings
  • " drives " and " since " are delimiters
Google Sheets CONCATENATE formula example with delimiter

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)
Google Sheets CONCATENATE formula example for a range

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.

Figure 1.1. Coupler.io is a solution to import data to Google Sheets Excel or BigQuery from different sources

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
Data set exported from BigQuery to Google Sheets

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)
Google Sheets CONCATENATE cells

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 with space

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)
Google Sheets CONCATENATE multiple cells (more than 2)

 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)
Google Sheets CONCATENATE with a new line

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 with a new line between data strings

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)
Google Sheets CONCATENATE string and number

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 with comma

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)
Google Sheets CONCATENATE date strings (unexpected result)

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"))
Google Sheets CONCATENATE date strings (with TEXT)

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.

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.

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"))
Google Sheets ARRAYFORMULA to concatenate columns

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:

concatenate ranges in Google Sheets vertically from two or more sheets

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.
Choose the spreadsheet, as well as the sheet(s)
  • Optionally, you can specify the range to concatenate in the selected sheets.
Specify the range to concatenate

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.+
Concatenate sheets with a patterned name

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 Destination (CONCATENATE)

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:

Concatenated columns in Google Sheets

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’.

concatenate formulatext

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")
concatenate formulatext example 1

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")
concatenate formulatext example 2

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"
concatenate formulatext ampersand example 1

and example 2:

="In total, I covered "&sum(B2:B13)&" miles"
concatenate formulatext ampersand example 2

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!

Back to Blog

Comments are closed.

Focus on your business
goals while we take care of your data!

Try Coupler.io