Concatenate in Google Sheets – Combine Cells Using Formula

Share Share Tweet Email Print

If you have content in different cells and you want to combine it, Google Sheets provide a couple of useful functions – such as CONCATENATE and JOIN.

In this tutorial, I will cover examples of how to concatenate in Google Sheets.

Before we jump to examples, let’s first learn about the concatenate operator and the concatenate function.

CONCATENATE Operator in Google Sheets

Ampersand sign (&) is the concatenate operator that you can use to combine cells in Google Sheets.

For example, suppose you have the first name and the last name as shown below:

Dataset to combine text in Google Sheets using ampersand

You can use the following formula to combine the first and the last name.

=A2&" "&B2

Combined names using the concatenate operator ampersand

Note that I have used a space character in between the reference for the first and the last name as I want these words to be separated by a space character. You can use any separator (also called delimiter) such as comma, hyphen, semi-colon, etc.

While ampersand concatenates operate works great, it’s useful only when you have a couple of cells to combine. In case you have a lot of cells that you need to concatenate in Google Sheets, it’s better to go for the functions.

CONCATENATE Function in Google Sheets

Concatenate function in Google Sheets allows you to quickly combine the values in cells.

Here is the syntax of the Concatenate function:

CONCATENATE(string1, [string2, …])

  • string1 – this is the initial string (first string).
  • string2 – this is the second string that you want to concatenate to the first one. You can specify

Now let’s see a couple of examples of using the CONCATENATE function in Google Sheets.

Example 1 – Concatenate with Space

Suppose you have the data set of first name and last name and you want to join these with space in between.

Dataset to combine text in Google Sheets using ampersand

Here is the formula that can get this done:

=CONCATENATE(A2," ",B2)

concatenate in Google Sheets - First and Last Name with Space

Note that the function has 3 arguments – the first name, a space character in double quotes, and the second name.

Example 2 – Concatenate With Comma

Suppose you have the same dataset of names, but instead of combining it with space in between, you want it in the following format: last name, first name.

Here is the formula that can get this done:

=CONCATENATE(B2,", ",A2)

concatenate in Google Sheets - First and Last Name with comma

Example 3 – Concatenate With a Running Number

Concatenate function can also be useful when you want to combine the text with a running number.

For example, in the dataset below, you want to get add a running number in front each name. So for the first name, it will be 01 – John Spike, for the second name it should be 02 – Brad Connor, and so on.

Here is the formula that can get this done:

=CONCATENATE(ROW()-1," - ",A2," ",B2)

concatenate a running number to names

Example 4 – Concatenate an Array of Cells

Suppose you have the same data set of names (as shown below):, however, in this

However, in this case, you want to combine the names and list these in a single cell (each name in a new line).

The following formula can get this done:

=JOIN(char(10),A2:A6&" "&B2:B6)

combine range in the same cell on different lines

Note that this is an array formula so use Control + Shift + Enter (hold the Control key and the Shift key and then press Enter). When you hit Control + Shift + Enter, it will automatically append ArrayFormula to the formula.

The formula uses CHAR(10) which would add a line break to the result of the formula.

This technique can also be useful when you have addresses with different parts in different cells in a row (such as house number in one cell, the street name on another, the city name on another and so on, and you want to combine it to create address labels.

You May Also Like the Following Google Sheets Tutorials:

Spreadsheet Expert at Productivity Spot | Website | + posts

Google Sheets and Microsoft Excel Expert.

You May Also Like the Following Tutorials:

Using IF Function in Google Sheets (with Examples) Countif Function in Google Sheets - Example 1How to Use COUNTIF Function in Google Sheets Fetch only the title from the feed of a websiteUsing IMPORTFEED in Google Sheets to Fetch Feed from URL Bar Sparkline in Google Sheets - rtlSparkline in Google Sheets – The Only Guide You Need

4 thoughts on “Concatenate in Google Sheets – Combine Cells Using Formula”

  1. I would like to test this to manage hashtags. I have Hashtags listed in 4 hashtag categories to use in certain combinations for certain social media posts.

    Also would not have to write out the ‘#’ every time.

    I am going to play around with it, any tips?

  3. How can I perform this action across multiple rows at once? I need to combine first and last name with a space in between, as per your first example, but I can’t figure out how to do it on every row. Do you just write out the same formula for each row…?

    • Garrison,

      Just drag down from the cell where the concatenate formula is. For example, using Example 1 provided above, you would drag from C2 down. Google Sheets will apply the formula to all the C cells

Comments are closed.

Popular posts: