Don’t forget to share this post

The CONCATENATE function in Google Sheets is used to merge multiple data strings into one. This allows you to combine text within multiple cells together. One of the most common examples, when the CONCATENATE function is used, is when you need to merge the two columns containing first names and last names into a single column. Combining texts within different cells can help to organize your spreadsheets more clearly, not only reducing the number of columns within your sheets but also allowing you to identify or manage your data more easily.

In this article, you’ll learn how to use CONCATENATE in Google Sheets, and provide a variety of examples both for how to use the function in different ways to achieve different results. You’ll learn how to use CONCATENATE to combine two entire columns, how to CONCATENATE cells with spaces, commas, and separators, how to combine the CONCATENATE function with the IF formula, and finally, alternatives to the CONCATENATE function.

How to use CONCATENATE in Google Sheets?

First, let’s take a look at the syntax for the CONCATENATE function in Google Sheets:

=CONCATENATE(data_string_1, “delimiter”,data_string_2, “delimiter”...)
  • data_string: reference to a cell (e.g. A1)
  • delimiter: any text you wish to add between the cell text (surrounded by quotation marks) - you can also leave these empty to create spaces, as you will see further into this article.

In the following example, I want to use the CONCATENATE function to combine the text within the ‘First name’ and ‘Last name’ columns together, in order to have a single column named ‘Full name’. These may also be referred to as CONCATENATE data strings. The following steps will show you how to CONCATENATE two columns in Google Sheets.

  1. 1. Open your Google Sheet and click on an empty cell where you want your combined text to be. In this example, I will add a new column next to the cells I want to combine.
How to use the CONCATENATE function in Google Sheets CONCATENATE combine in new column
How to use the CONCATENATE function in Google Sheets - CONCATENATE combine in new column
  1. 2. Enter the CONCATENATE function, making sure you include the cells you wish to combine. In this case, my formula will be =CONCATENATE(A2,B2), combining the data from ‘A3’ and ‘B3’ into a single cell.
How to use the CONCATENATE function in Google Sheets combine two cells
How to use the CONCATENATE function in Google Sheets - Combine two cells
  1. 3. There are 3 ways to apply the CONCATENATE function to the rest of your rows:
    1. a. Google Sheets may trigger an autofill for the rest of your rows. You can click the tick to apply
How to use the CONCATENATE function in Google Sheets Autofill CONCATENATE
How to use the CONCATENATE function in Google Sheets - Autofill CONCATENATE
  1. b. Select the cell containing your combined text. Click on the bottom right-hand corner of the cell and drag it down over the rest of your rows to apply the function.
How to use the CONCATENATE function in Google Sheets Apply CONCATENATE function to dataset
How to use the CONCATENATE function in Google Sheets - Apply CONCATENATE function to dataset
  1. c. Substitute the cell names with a cell range. For example, to apply the CONCATENATE function to all of my A and B cells, I would enter =CONCATENATE(A:B).
How to use the CONCATENATE function in Google Sheets CONCATENATE data string range
How to use the CONCATENATE function in Google Sheets - CONCATENATE data string range

As you can see from the screenshot below, the text within the ‘First name’ and ‘Last name’ columns has been combined.

How to use the CONCATENATE function in Google Sheets Basic use of CONCATENATE
How to use the CONCATENATE function in Google Sheets - Basic use of CONCATENATE
Linking Google Sheets How to Reference Another Sheet
Linking Google Sheets: How to Reference Another Sheet?

Sometimes you have to reference or merge data from multiple sheets or spreadsheets. Here's how to easily link multiple Google Sheets

READ MORE

How to CONCATENATE cells with spaces in Google Sheets?

As you may have noticed from the screenshot above, the CONCATENATE function has not added spaces between the text from each cell. This may cause issues with legibility, especially when you share your Google Sheets file with someone else.

However, there is an easy way you can CONCATENATE cells with spaces. Simply add empty strings (“ “) as the delimiter in your formula, in order to create spaces between your text in a combined cell.

  1. 1. Click on the cell you wish to add your combined cells to. Enter your CONCATENATE formula, making sure to reference the cells you want to combine, and also the spaces. It should look something like this: =CONCATENATE(A2,“ ”,B2)
How to use the CONCATENATE function in Google Sheets CONCATENATE cells with spaces
How to use the CONCATENATE function in Google Sheets - CONCATENATE cells with spaces
  1. 2. Apply the CONCATENATE function to the rest of your rows, either using the autofill, drag-down, or data range method highlighted in the section above.
How to use the CONCATENATE function in Google Sheets Apply CONCATENATE with spaces to dataset
How to use the CONCATENATE function in Google Sheets - Apply CONCATENATE with spaces to dataset

As you can see, the combined cells now include a space between each text.

How to CONCATENATE cells with commas in Google Sheets?

Similar to adding spaces in the CONCATENATE function, to add a comma, simply add “,” as the delimiter to your formula. In this example, let’s say I want to format my ‘Full name’ column so that the last name comes first, followed by a comma and the first name (i.e. ‘Last name’, ‘First name’).

  1. 1. Click on the cell you wish to add your combined cells to. Enter your CONCATENATE formula, making sure to reference the cells you want to combine, and also the commas. It should look something like this: =CONCATENATE(B2,“, ”,A2)
How to use the CONCATENATE function in Google Sheets CONCATENATE cells with comma
How to use the CONCATENATE function in Google Sheets - CONCATENATE cells with comma
  1. 2. Apply the CONCATENATE function to the rest of your rows, either using the autofill, drag-down, or data range method highlighted in the how to use CONCATENATE in Google Sheets section.
How to use the CONCATENATE function in Google Sheets Apply CONCATENATE with commas
How to use the CONCATENATE function in Google Sheets - Apply CONCATENATE with commas

As you can see, the combined cells now include a space between each text.

How to CONCATENATE cells with separators in Google Sheets?

Sometimes, combining cells together in Google Sheets can get confusing, as there may not be enough context that determines the relationship between the two. For example, let’s say I wanted to combine the cells that refer to the order information - ‘Product order’, ‘Quantity’ and ‘Total’. Combining these on their own may be quite confusing without any additional context.

Using separators, i.e. text between each cell as a delimiter, I can add more information to make the relationship between each product clear. In this example, I want to add separators so that my combined cell contains the following: (‘Quantity’) orders of (‘Product order’), costing (‘Total’).

  1. 1. Click on the cell you wish to add your combined cells to. Enter your CONCATENATE formula, making sure to reference the cells you want to combine, and also adding your separators. In this example, my CONCATENATE formula would look like this: =CONCATENATE(E2," orders of ",D2," ,costing ",F2).
How to use the CONCATENATE function in Google Sheets CONCATENATE with separators
How to use the CONCATENATE function in Google Sheets - CONCATENATE with separators
  1. 2. Apply the CONCATENATE function to the rest of your rows, either using the autofill, drag-down, or data range method highlighted in How to use CONCATENATE in Google Sheets section.
How to use the CONCATENATE function in Google Sheets Apply CONCATENATE with separators
How to use the CONCATENATE function in Google Sheets - Apply CONCATENATE with separators

As you can see, adding text to create separators between my formula has provided the context I need to better understand my data.

How to combine multiple columns using CONCATENATE?

The CONCATENATE function is also really easy to apply to multiple columns, as you can see in the section above. Simply continue adding your cell references to the formula, separated by a comma. Here is another quick example of how to do it.

Let’s say I want to combine all of the customer data into one cell - ‘First name’, ‘Last name’, and ‘Address’.

  1. 1. Click on the cell you wish to add your combined cells to. Enter your CONCATENATE formula, making sure to reference the cells you want to combine, and also adding your delimiters (as spaces, commas, or separators). In this example, my CONCATENATE formula would look like this: =CONCATENATE(A2, " ",B2,", from ",H2).
How to use the CONCATENATE function in Google Sheets CONCATENATE multiple columns
How to use the CONCATENATE function in Google Sheets - CONCATENATE multiple columns
  1. 2. Apply the CONCATENATE function to the rest of your rows, either using the autofill, drag-down, or data range method highlighted in How to use CONCATENATE in Google Sheets section.
How to use the CONCATENATE function in Google Sheets Apply CONCATENATE to multiple columns
How to use the CONCATENATE function in Google Sheets - Apply CONCATENATE to multiple columns

How to Import CSV to Google Sheets Automatically?

If you need to open CSV files in Google Sheets, you can either do it manually or automatically. Here's how to import CSV files into Google Sheets.

READ MORE
How to Import CSV to Google Sheets Automatically

Conditional Concatenate using IF formula in Google Sheets?

You can also combine the CONCATENATE function with other formulas in order to apply more complex conditions to your data. For example, you can use CONCATENATE with the IF formula to only combine certain cells that meet specific criteria.

For example, let’s say I want to check whether I can approve the order status for each product. If the quantity of each product is over 8, then the order is automatically delayed. I can use the CONCATENATE and IF functions together so that any orders over 8 return an “Automatically delayed” text. If an order is below 8, then the ‘Product order’ and ‘Quantity’ cells will be combined.

  1. 1. Click on the cell you wish to add your combined cells to. Create a formula starting with your IF formula, followed by the CONCATENATE formula. In this example, the formula would look like this: =IF(E2>8,"Automatically delayed", CONCATENATE(D2,", ",E2))
How to use the CONCATENATE function in Google Sheets CONCATENATE and IF formulae
How to use the CONCATENATE function in Google Sheets - CONCATENATE and IF formulae
  1. 2. Apply the CONCATENATE function to the rest of your rows, either using the autofill or drag-down highlighted in the how to use CONCATENATE in Google Sheets section.
How to use the CONCATENATE function in Google Sheets Apply CONCATENATE and IF functions to data
How to use the CONCATENATE function in Google Sheets - Apply CONCATENATE and IF functions to data

As you can see, every order with a quantity above 8 has returned an “Automatically delayed” text. Every order below this value has successfully combined the ‘Product order’ and ‘Quantity’ cells.

Alternatives to the CONCATENATE FUNCTION: Ampersand and Join

There are alternatives to the CONCATENATE function in Google Sheets. These work in almost exactly the same way, so it’s useful to try these to see which one you prefer.

CONCATENATE alternative: Use Ampersand (&) in Google Sheets

You can replace your CONCATENATE function by adding an ampersand (&) between each cell reference. You can continue using delimiters to add spaces, commas or texts between your cell content.

  1. 1. Click on the cell you wish to add your combined cells to. Make sure to add an ampersand between each cell reference and delimiter: =A2&" "&B2
How to use the CONCATENATE function in Google Sheets CONCATENATE alternative Ampersand
How to use the CONCATENATE function in Google Sheets - CONCATENATE alternative - Ampersand
  1. 2. Apply to the rest of your rows, either using the autofill or drag-down highlighted in the ow to use CONCATENATE in Google Sheets section.
How to use the CONCATENATE function in Google Sheets Ampersand alternative to CONCATENATE
How to use the CONCATENATE function in Google Sheets - Ampersand alternative to CONCATENATE

CONCATENATE alternative: Use the JOIN function in Google Sheets

The JOIN function is a similar formula used to combine cells. However, the JOIN function only works with cells that only contain text. You can still add a delimiter to the formula, but this goes at the beginning of the formula.

The syntax looks like this:

=JOIN(delimiter, data_string_1, data_string_2..)
  1. 1. Click on the cell you wish to add your combined cells to. Make sure to reference the correct cells you want to combine (text only) and add the delimiters. In this example of joining the ‘First name’ and ‘Last name’ cells together, the formula will look like this: =JOIN(“ “,A2,B2)
How to use the CONCATENATE function in Google Sheets CONCATENATE alternative JOIN function
How to use the CONCATENATE function in Google Sheets - CONCATENATE alternative - JOIN function
  1. 2. Apply to the rest of your rows, either using the autofill or drag-down highlighted in How to use CONCATENATE in Google Sheets section.
How to use the CONCATENATE function in Google Sheets JOIN formula
How to use the CONCATENATE function in Google Sheets - JOIN formula

How to manage Google Sheets data with Layer?

Layer is a collaboration platform for spreadsheets that works on top of your existing Excel files and Google Sheets. Share parts of your spreadsheet, collect and consolidate data, and review changes to make collaboration seamless and more efficient while keeping full control over your data. Using Layer, you can:

  • Manage Access: Give spreadsheet access on a tab or cell level to relevant stakeholders.
  • Collaborate: Automate tasks, set deadlines, and communicate on top of your files.
  • Review & Track: Consolidate input, track changes, and restore previous versions.

Sign up to get started with Layer right now or schedule an onboarding call so we can help you set up and share your spreadsheet flows.

Conclusion

The CONCATENATE function in Google Sheets is a great tool to use when you need to organize or manage your data; not only can it make your spreadsheet easier to understand, but it can also add important context to your groups of data.

Although there are alternatives to the CONCATENATE function, such as using the ampersand (&) or the JOIN function, the CONCATENATE function offers more flexibility and complexity in your actions. Not only can you combine cells containing text, but also CONCATENATE a date, numbers, or other formats.

What’s more, you can combine the CONCATENATE function with other complex formulas like the IF formula, to create complex conditions to organize your data.

Looking for more information on how to better combine, transfer or reference your spreadsheet data? Take a look at our related articles:

Hady ElHady
Hady is Content Lead at Layer.

Hady has a passion for tech, marketing, and spreadsheets. Besides his Computer Science degree, he has vast experience in developing, launching, and scaling content marketing processes at SaaS startups.

Originally published Feb 21 2022, Updated May 30 2022