Concatenate in Google Sheets: Concatenate Multiple Cells using Separator, Comma

by

Concatenate in Google Sheets: Concatenate is a formula that can be used to combine data from multiple data cells into a single cell. With the help of concatenating formula, we can combine multiple cells with separator, comma and much more options in Google Spreadsheet. In this article, let’s understand how to combine data from multiple cells into a single cell with important Google Sheet tips. Read on to find more.

Table of Contents

Concatenate Formula Syntax in Google Sheets

The Google Sheets Concatenate function allows one to quickly combine data in cells.

The Concatenate function’s syntax is as follows:

CONCATENATE(string1, [string2, …])

  • string1: This is the first string
  • string2: The second string that should be concatenated with the first. You have to specify the option how it should be combined. For example, with space, separator, comma and so on.

Now let’s understand how to use the concatenate function to combine the data from multiple cells into a single cell in Google Spreadsheet.

Concatenate Multiple Cells with Space in Google Sheets

Follow the steps listed below to concatenate multiple cells with space in Google Sheets:

  • Step 1: Select the cell where you would like to combine the multiple cells.
  • Step 2: Now enter the formula as =CONCATENATE(A2&,” “,&B2).
  • Step 3: Press “Enter“. You will see the results as shown below.

Concatenate in Google Sheets

You can also use the formula =CONCATENATE(A2,” “,B2) to concatenate the two cells into a single cell with space.

Google Spreadsheet Concatenate with Separator

We can even combine multiple cells into a single cell with the help of a separator in Google Sheets. Here we will use Comma as the separator to combine the multiple cells. The steps to concatenate cells with separator are explained below:

  • Step 1: Select the cell where you would like to combine the cell with a separator.
  • Step 2: Now enter the formula as =CONCATENATE(B2,”, “,A2).
  • Step 3: Press the “Enter” button to see the results.

Concatenate in Google Sheets1

Google Sheets Concatenate with Running Number

When you wish to merge text with a running number, the concatenate method comes in handy.

You might want to add a running number in front of each name in the dataset. For example, the first name should be 01 followed by the name. Follow the steps given below to concatenate the name with the running number in Google Sheets:

  • Step 1: Select the cells where you would like to concatenate the names with the running numbers.
  • Step 2: Enter the formula as =CONCATENATE(ROW()-1,” – “,A2,” “,B2).
  • Step 3: Press “Enter“. Now you will see the results. Now drag the formula applied cell to see the results in the entire sheet.

Concatenate in Google Sheets2

Concatenate Array of Cells into Single Cell

Assume you have the same collection of names (as in the example below). In this case, you’ll want to group the names together and list them in one cell (each name in a new line). The concatenate formula helps to get this done. The steps to concatenate the array of cells into Single-cell are explained below:

  • Step 1: Select the cells where you would like to combine the array of cells.
  • Step 2: Now enter the formula as =ArrayFormula(JOIN(char(10),A2:A6&” “&B2:B6)).
  • Step 3: Press “Enter” to see the results.

Concatenate in Google Sheets3

Leave a Comment