An Easy Guide to the CONCATENATE Google Sheets Function

If you’ve just switched over to Google Sheets from Excel, you’re likely already familiar with the CONCAT function. While you can still use CONCAT in Google Sheets, you could consider the CONCATENATE Google Sheets function an upgraded version of it.

Even if you’re completely unfamiliar with concatenation, this guide will break it down into easy-to-understand sections. Read on to learn how to concatenate in Google Sheets.

What is the Google Sheets Concatenate Strings Function?

Concatenate means to connect or join something together in a series or a chain. In Sheets, the function will allow you to combine more than one value from different cells into one. This can speed up tasks like combining first and last names to create a full name or writing registration numbers.

This function works similarly to the CONCAT function but vastly improves upon it. CONCAT only allows you to join up to 2 values, whereas CONCATENATE enables you to join multiple values.

CONCATENATE Google Sheets Syntax

Now that we know what the CONCATENATE Google Sheets function does, let’s take a look at to formula used to append the strings in your spreadsheet:

=CONCATENATE(str1, str2, …)

Here are the parameters used in CONCATENATE:

• str1: this parameter is the first initial value you wish to display
• str2: this defines the additional optional string you want to show after the initial string.

The strings or addresses of the cells added in the function’s formula can refer to a spreadsheet data set. You may also write the cell addresses as a 2d array. You can keep adding additional str parameters depending on your requirements.

If you specify a cell range in the str parameter with a height and width larger than 1, the cells will be appended across the rows first rather than the columns.

For example, if you add the cell range as A1:B3, it would be the same as writing the formula as CONCATENATE(A1, B1, A2, B2, A3, B3). This will output the values as A1 B1, A2 B2, and A3 B3.

Difference Between CONCATENATE and CONCAT in Sheets

To understand the differences between Google Sheets CONCAT strings and CONCATENATE formula, let’s first take a look at the syntax for the CONCAT formula in Sheets:

=CONCAT(val1, val2)

Here, the val1 parameter defines the first value in the sequence, and val2 represents the value following the val1 parameter. You may spot the difference instantly as this function only allows you to have two parameters, making it equivalent to the & operator.

The CONCATENATE formula is more complex than the CONCAT formula as it will allow more flexibility if you don’t wish to combine the data in a different format in the cells. CONCAT won’t allow you to add spaces or additional text. In contrast, CONCATENATE will allow you to add more than two parameters and additional characters.

For example, if you wanted to join the text strings in A1 and B1 and add a space in between, you could do so with a formula like this:

=CONCATENATE(A1,” “,B1)

CONCAT does not have similar functionality. We’ll cover this in more detail in the examples.

How to Use CONCATENATE in Sheets

Now that we know how the formula works, let’s look at some examples of CONCATENATE in action.

The Basics: How to Use Concatenate in Google Sheets

In this example, we will look at the most basic use of the CONCATENATE function. We have a list of first and last names and want to combine them to display them as full names in a single cell.

Here’s how to concatenate in Sheets:

1. Click the cell you want to enter the formula in.
2. Enter the starting part of CONCATENATE, which is going to be =CONCATENATE(
3. Now, we need to add the first parameter, which in this example is the cell address containing the first name. In this example, it is A2.
4. Add Comma ( , ) to separate the parameters.
5. Now, write the second parameter. In this example, it is the B2 cell range.
6. Finally, add Closing brackets “ ) ” to finish the formula.
7. Press the Enter button to use the formula.

Once you press Enter, the data from the two cells will be displayed together in a single cell. Chances are that you may have a list of data in your spreadsheet. Inputting the formula, again and again can be tedious. To apply the formula in the subsequent cells, click on the cell containing the initial formula and click and drag the blue dot at the bottom-right side of the box.

Note: There are limits to concatenating numbers. The character limit is 255 for any joined strings using this function.

Adding Spaces to CONCATENATE (Google Sheets Concatenate With Separator)

In the previous method, you can see that the two names are written together without a space in between. Let’s look at how you can add a space between the first and the last name using CONCATENATE in Sheets.

Here’s how to perform a Google spreadsheet concatenate with a separator:

1. Click the cell you want to enter the formula in.
2. Enter the starting part of CONCATENATE, which is going to be =CONCATENATE(
3. Now, we need to add the first parameter, which in this example is the cell address containing the first name. In this example, it is A2.
4. Add Comma ( , ) to separate the parameters.
5. Now, write the separator inside quotation marks. In this example, we will add a space. So, we wrote it as “ ”.
6. Add another Comma ( , ) and write the third parameter, which is B2.
7. Finally, add Closing brackets “ ) ” to finish the formula.
8. Press the Enter button to use the formula.

Note: A Google spreadsheet can concatenate with any separator as long as it’s in quotes. For example, it could be “ “, “-”, “and then”, or even “anjsbkj HGai-45”, anything you want it to be.

Nested Functions With CONCATENATE

You may also execute other functions inside CONCATENATE formula, which will vastly increase the things you can do in your spreadsheet with this function. In this example, let’s look at how you can add numbers beside the names in your spreadsheet.

Here are the steps to follow if you want your Google Sheet to concat strings with numbers:

1. Click the cell you want to enter the formula in.
2. Enter the starting part of CONCATENATE, which is going to be =CONCATENATE(
3. Now, we will enter the first parameter wish will be used to add the numbers. Here, we will enter ROW()-1. This is because our range starts in row 2, so adding the row numbers as minus one gives the value of the list position.
4. Add Comma ( , ) to separate the parameters.
5. Now, we need to add the second parameter, which in this example, is the symbol we wish to use as a divider (separator). In this example, it is “- “. Don’t forget the quotation marks.
6. Add Comma ( , ) and enter the third parameter, which is the cell address containing first name. In this example, it is A2.
7. Add another Comma ( , ) and add a space. Write it as “ ” with quotation marks.
8. Add another Comma ( , ) and write the fifth parameter, which is B2.
9. Finally, add Closing brackets “ ) ” to finish the formula.
10. Press the Enter button to use the formula.

How to Use JOIN Instead

There are a few instances where using an alternate function can be better than the CONCAT and CONCATENATE functions. Using the JOIN function can be a viable alternative if your spreadsheet has large arrays of data. It allows you to specify a delimiter character, like commas and spaces, which can be placed automatically.

The syntax for JOIN is:

=JOIN(delimiter, val1, val2, …)

The delimiter parameter defines the string or character between the concatenated values. The val parameters define the values to be concatenated.

Frequently Asked Questions

What Does CONCATENATE Do in Sheets?

The CONCATENATE function will allow you to combine values from different cells into one. This can speed up tasks like combining first and last names to create a full name. This function works similarly to the CONCAT function. CONCAT only allows you to join up to 2 values, whereas CONCATENATE will enable you to join multiple values.

How Do I CONCATENATE Two Columns in Sheets?

To CONCATENATE the data in two different columns, click on the cell and enter the starting part of CONCATENATE, which is =CONCATENATE(.Click the cell references for each parameter you wish to join, separated by a comma. Finally, add closing brackets to finish up the formula. Press the Enter button to apply the formula.

How Do You CONCATENATE a Range of Cells in Sheets?

To use CONCATENATE in Sheets, click and select the cells you wish to combine. Then, click on Insert in the top bar, click on Function, and then on All. There, click on CONCATENATE to insert the function into your spreadsheet. You may also add spaces by adding two quotation marks as a parameter and a space in between.

What’s the Difference Between CONCAT and CONCATENATE?

The CONCATENATE formula is a bit more complex than the CONCAT formula as it will allow more flexibility. CONCAT won’t allow you to add spaces or additional text. In contrast, CONCATENATE will allow you to add more than two parameters and additional characters. You can also join more than two strings with the CONCATENATE.

How Do You CONCATENATE 3 Columns in Sheets?

To use the CONCATENATE formula with three columns, click and select all three cells. Then, click on Insert in the top bar, click on Function, and then on All. There, click on CONCATENATE to insert the function into your spreadsheet. You can use the fill handle to apply the formula in the entire column.

Can Google Sheets Concatenate With a Comma?

Yes, the CONCATENATE Google Sheets function works with any separator, including commas. You just have to include it in quotes in between the cell references you wish to concatenate. For example, if you were joining cells A1 and B1 you could use the following formula:

=CONCATENATE(A1,”,”,B1)

You could also add a space with the comma in the separator like so:

=CONCATENATE(A1,”, ”,B1)

Wrapping up the Concatenate Strings Google Sheets Guide

By now, you should be able to perform most CONCATENATE Google Sheets functions, recognize its superiority to the CONCAT function, and when to use the JOIN function instead. Whichever method you use for Google Sheets to combine text from two cells, feel free to ask in the comments if you still need help.