An Easy Guide to the CONCATENATE Google Sheets Function – Spreadsheet Point

If you just switched from Excel to Google Sheets, you’re probably already familiar with the CONCAT function. You can still use CONCAT in Google Sheets, but you can think of the CONCATENATE Google Sheets function as an upgraded version of it.

Even if you’re completely new to concatenation, this guide breaks it down into easy-to-understand sections. Read this article on how to concatenate in Google Sheets.

What is the Google Sheets Concatenate Strings Function?

Concatenate means to connect or combine something in a row or chain. In a spreadsheet, this function allows you to combine multiple values ​​from different cells into one. This speeds up tasks like combining first and last names to create a full name or writing a registration number.

This function works similarly to the CONCAT function, but with significant improvements. CONCAT can only combine up to two values, but CONCATENATE can combine multiple values.

CONCATENATE Google Sheets Syntax

Now that we know what the Google Sheets CONCATENATE function does, let’s look at the formula used to add strings to the spreadsheet:

=CONCATENATE(str1, str2, …)

The parameters used with CONCATENATE are:

• str1: This parameter is the first initial value to display
• str2: This defines an additional optional string to display after the first string.

strings or cell addresses added to function formulas can refer to spreadsheet data sets. Cell addresses can also be written as a two-dimensional array. You can keep adding additional str parameters according to your requirements.

If the Str parameter specifies a cell range with a height and width greater than 1, the cells are added first in whole rows instead of columns.

For example, if you added 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 difference between the CONCAT string and the CONCATENATE formula in Google Sheets, let’s first look at the syntax of the CONCAT formula in Sheets:

=CONCAT(val1, val2)

where the val1 parameter defines the first value in the sequence and val2 represents the value following the val1 parameter. Since this function only allows two parameters, it is equivalent to the & operator, so you may notice the difference immediately.

The CONCATENATE formula is more complex than the CONCAT formula because it gives you more flexibility when you don’t want to combine differently formatted data in a cell. CONCAT does not allow adding spaces or additional text. In contrast, CONCATENATE lets you add more than two parameters and additional characters.

For example, if you wanted to combine the A1 and B1 text strings and add a space in between, you could use a formula like this:

=CONCATENATE(A1,” “,B1)

CONCAT has no similar functionality. This will be explained in detail in an example.

How to Use CONCATENATE in Sheets

Now that we understand how formulas work, let’s look at some examples of CONCATENATE in action.

The Basics: How to Use Concatenate in Google Sheets

This example looks at the most basic usage of the CONCATENATE function. I have a list of first and last names and would like to combine them and display them as full names in a single cell.

Here’s how to concatenate in a spreadsheet:

1. Click the cell where you want to enter the formula.
2. Enter the beginning of CONCATENATE. This becomes =CONCATENATE(
3. Now we need to add the first parameter. In this example, it is the address of the cell containing the name. In this example it is A2.
4. Add a comma (,) to separate the parameters.
5. Then write the second parameter. In this example, it is the B2 cell range.
6. Finally, add a right parenthesis “)” to complete the formula.
7. Press the Enter button to use the formula.

Press Enter to combine the data from the two cells into one cell. You may have a list of data in your spreadsheet. Typing formulas over and over again can be tedious. To apply the formula to subsequent cells, click the cell containing the first formula, then click and drag the blue dot in the lower right corner of the box.

Note: There are restrictions on concatenating numbers. The character limit for strings concatenated using this function is 255.

Adding Spaces to CONCATENATE (Google Sheets Concatenate With Separator)

In the previous method, we see that the two names are written together with no space in between. Let’s see how to use CONCATENATE in a spreadsheet to add a space between first and last names.

Here’s how to concatenate Google Sheets with a delimiter:

1. Click the cell where you want to enter the formula.
2. Enter the beginning of CONCATENATE. This becomes =CONCATENATE(
3. Now we need to add the first parameter. In this example, it is the address of the cell containing the name. In this example it is A2.
4. Add a comma (,) to separate the parameters.
5. Then enclose the delimiter in quotation marks. In this example, we add a space. So I wrote “ ”.
6. Add another comma (,) and write the third parameter, B2.
7. Finally, add a right parenthesis “)” to complete the formula.
8. Press the Enter button to use the formula.

Note: Google Sheets can concatenate with any delimiter as long as it is quoted. For example, ” “, “-“, “and”, or even “anjsbkj HGai-45.

Nested Functions With CONCATENATE

You can also perform other functions within the CONCATENATE formula, greatly increasing what you can do with your spreadsheet using this function. In this example, let’s see how to add numbers next to names in a spreadsheet.

Here are the steps for concatenating strings and numbers in Google Sheets:

1. Click the cell where you want to enter the formula.
2. Enter the beginning of CONCATENATE. This becomes =CONCATENATE(
3. Then enter the first parameter used to add the numbers. Enter ROW()-1 here. This is because the range starts at row 2, so adding the row number as minus 1 gives the list position value.
4. Add a comma (,) to separate the parameters.
5. Now we need to add a second parameter. In this example, this is the symbol to use as a divider (separator). In this example it is “-“. Don’t forget the quotation marks.
6. Add a comma (,) and enter the third parameter, which is the cell address containing the first name. In this example it is A2.
7. Add more commas (,) and add spaces. Enclose it in quotation marks, such as.
8. Add another comma (,) and write the fifth parameter, B2.
9. Finally, add a right parenthesis “)” to complete the formula.
10. Press the Enter button to use the formula.

How to Use JOIN Instead

Using an alternative function may be better than the CONCAT or CONCATENATE functions. If your spreadsheet contains large arrays of data, using the JOIN function is a good alternative. You can specify delimiters such as commas and spaces that are automatically placed.

The JOIN syntax is:

=JOIN(delimiter, val1, val2, …)

The Delimiter parameter defines strings or characters between concatenated values. The val parameter defines the value to be concatenated.

Frequently Asked Questions

What Does CONCATENATE Do in Sheets?

You can use the CONCATENATE function to combine different cell values ​​into one. This speeds up tasks such as combining first and last names to form a full name. This function works similarly to his CONCAT function. CONCAT can only combine up to two values, but CONCATENATE can combine multiple values.

How Do I CONCATENATE Two Columns in Sheets?

To concatenate data from two different columns, click a cell and type =CONCATENATE(at the beginning of CONCATENATE. Click the cell reference for each parameter you want to concatenate, separating them with commas. Finally, Add a closing parenthesis to end the formula and press the Enter button to apply the formula.

How Do You CONCATENATE a Range of Cells in Sheets?

To use CONCATENATE in your sheet, click to select the cells you want to merge. Then click Insert on the top bar, click Function, then click All. So click “CONCATENATE” to insert the function into the spreadsheet. You can also add spaces by adding two quotes as a parameter with a space between them.

What’s the Difference Between CONCAT and CONCATENATE?

The CONCATENATE expression is slightly more complicated than the CONCAT expression due to its greater flexibility. CONCAT does not allow adding spaces or additional text. In contrast, CONCATENATE allows you to add more than two parameters and additional characters. You can also use CONCATENATE to combine three or more strings.

How Do You CONCATENATE 3 Columns in Sheets?

To use the CONCATENATE formula in three columns, click to select all three cells. Then click Insert on the top bar, click Function, then click All. So click “CONCATENATE” to insert the function into the spreadsheet. Fill handles can be used to apply formulas across columns.

Can Google Sheets Concatenate With a Comma?

Yes, the CONCATENATE function in Google Sheets works with any delimiter, including commas. Just put quotes between the cell references you want to concatenate. For example, to merge cells A1 and B1, you can use the following formula:

=CONCATENATE(A1,”,”,B1)

You can also add spaces using commas as delimiters, like this:

=CONCATENATE(A1,”,”,B1)

Wrapping up the Concatenate Strings Google Sheets Guide

By now, you should be able to do most of the CONCATENATE function in Google Sheets, recognize the advantages of the CONCATENATE function over the CONCAT function, and when to use the JOIN function instead. Whichever method you use to combine text from two cells in Google Sheets, if you need help, feel free to ask in the comments.