Back to Blog

Google Sheets CONCATENATE Function to Combine Data from Multiple Cells Together

The cell A1 contains “Jane“, the cell B1 contains “Pontiac“, and the 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 combines 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 strings

CONCATENATE Syntax 

=CONCATENATE(data_string#1,"delimiter",data_string#2,"delimiter",data_string#3...)
  • data_string – a reference to a cell
  • delimiter can be a symbol or string that separates the data strings

You can also use CONCATENATE without delimiters. In this case, you just need to specify the data range, the values from which will be merged.

=CONCATENATE(data_range)

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

And here is the formula for merging values without delimiters:

=CONCATENATE(A1:C1)

The CONCATENATE 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).

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

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

If you want to concatenate each data string from a new line, you’ll need to add &char(10) to your CONCATENATE 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 ARRAYFORMULA to concatenate columns

Unfortunately, CONCATENATE nested with ARRAYFORMULA will return all values from columns merged in a row.

But you can use 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 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“, name it, and complete these three steps:

Set up Source (Google Sheets)

  • Choose Google Sheets as the source. Click “Continue“. Then you’ll need to connect your Google account or choose one from the list if you’ve used Coupler.io before. Click “Continue“.
  • Choose the spreadsheet, as well as the sheet(s) the columns from which you want to concatenate. Click “Continue“.

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.+
  • Optionally, you can specify the range to concatenate in the selected sheets. Click “Proceed to Destination Settings“.

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 import your data range 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 + 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 CONCATENATE 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.

Access your data
in a simple format for free!

Start Free