Do you want to merge cells in Google Sheets? Let’s talk about the CONCATENATE function. It allows you to combine the contents of multiple cells. Just like the CONCATENATE function in Excel, there is also a CONCATENATE function in Google Sheets. Note that it is different from the new CONCAT function. They are different but work in a similar way. I’ll show you how to use them. Read on to learn how to merge cells in Google Sheets.
What is the CONCATENATE function in Google Sheets?
In spreadsheet applications like Google Sheets, “concatenate” refers to the process of joining or merging elements into a sequence. In Sheets, the CONCATENATE function is a valuable tool that allows you to combine values from different cells into a single cell.
This is useful for tasks such as quickly creating full names by merging first names and last names, or consolidating registration numbers. I rely on this feature to streamline data synthesis, ensuring accurate and consistent representation in my spreadsheets.
Difference between CONCATENATE and CONCAT in Sheets
You may have heard of the CONCAT function in Excel. It is very useful as it combines the content of two cells. You can also use CONCAT in Google Sheets. It differs from the CONCATENATE function significantly. You will use CONCAT to combine two cells, while CONCATENATE allows you to join multiple values. This is handy when you want to add the content of multiple cells at once.
CONCATENATE Syntax
Now that we know what the CONCATENATE function does in Google Sheets, let’s look at the formula used to add string values to your spreadsheet:
=CONCATENATE(str1, str2, ...)
Here are the parameters used in CONCATENATE:
str1: This parameter is the first initial value you want to display.str2: This sets the optional additional string you want to display after the initial string.
The strings or cell addresses added in the function formula can refer to a range of data within the spreadsheet. You can also write cell addresses in the form of a 2D array. You can add additional str parameters based on your needs.
If you specify a range of cells in the str parameter with a height and width greater than 1, the cells will be added first horizontally rather than vertically.
For example, if you add the cell range A1:B3, it would be equivalent to writing the formula =CONCATENATE(A1, B1, A2, B2, A3, B3). This will display the values A1 B1, A2, B2, and A3 B3.
CONCAT Syntax in Google Sheets
To understand the differences between the CONCATENATE and CONCAT functions in Sheets, let’s first look at the syntax of the CONCAT formula:
=CONCAT(val1, val2)
Here, the val1 parameter defines the first value in the sequence, and val2 represents the value following the val1 parameter. You’ll notice the difference immediately because this function only allows you to have two parameters, which is equivalent to the ampersand (&) operator in Excel.
Main Difference between CONCAT and CONCATENATE
The CONCATENATE formula is more complex than the CONCAT formula because it provides greater flexibility if you don’t want to combine the data in a different format within cells. CONCAT doesn’t allow you to add spaces or additional text. On the other hand, CONCATENATE allows you to add more than two parameters and additional characters. This is very useful when working with names and addresses.
For example, if you want to join text strings in cells A1 and B1 and add a space between them, you can do so with a formula like this:
=CONCATENATE(A1, " ", B1)
CONCAT doesn’t offer this similar functionality. We will go into more detail on this in the examples.
How to Use CONCATENATE in Sheets: Examples
Now that we know how the formula works, let’s look at some specific examples of CONCATENATE in action. Below, I will show you specific examples of how to combine cells using the function. I will include screenshots and step-by-step instructions.
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 names and last names, and we want to combine them to display them as full names in a single cell.
Here’s how to concatenate in Sheets:
- Click on the cell where you want to enter the formula.
- Enter the first part of CONCATENATE, which will be
=CONCATENATE( - Next, add the first parameter, which in this example is the cell address containing the first name. In this example, it is A2.
- Add a comma (
,) to separate the parameters. - Now, write the second parameter. In this example, it is the cell range B2.
- Finally, add a closing parenthesis
)to end the formula. - Press Enter to use the formula.
Once you press Enter, the data from both cells will be displayed together in a single cell. You likely have a list of data in your spreadsheet. Entering the formula again can be tedious. To apply the formula to the following cells, click on the cell containing the initial formula, then click and drag the blue dot located at the bottom right of the box.
Note: There are limits to concatenating numbers. The character limit is 255 for all strings joined using this function.
Adding Spaces to CONCATENATE (Google Sheets Concatenation with Separator)
In the previous method, you can see that the two names are written together without a space between them. Let’s see how you can add a space between the first name and last name using CONCATENATE in Sheets. Note that this can be particularly useful when working with names.
For example, you might extract a set of data from the US Census. By using the first name and last name columns, you could combine the names and add spaces between them. Of course, that’s just one of the reasons you might want to add a space when concatenating cells. It is also a common solution when concatenating dates and times.
Want to see what that looks like? Here’s how to perform Google Sheets concatenation with a separator (with screenshots):
- Click on the cell where you want to enter the formula.
- Enter the first part of CONCATENATE, which will be
=CONCATENATE( - 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.
- Add a comma (
,) to separate the parameters. - Now, write the separator within quotation marks. In this example, we will add a space. So we wrote it as
" ". - Add another comma (
,) and write the third parameter, which is B2. - Finally, add a closing parenthesis
)to end the formula. - Press Enter to use the formula.
Note: A Google Sheets can concatenate with any separator as long as it is in quotes. For example, it could be ” “, “-“, “and then,” or even “anjsbkj HGai-45,” anything you want.
Nested Functions with CONCATENATE
You can also perform other functions within the CONCATENATE formula, allowing you to do much more in your spreadsheet with this function. In this example, let’s see how you can add numbers next to names in your spreadsheet.
Here are the steps to follow if you want to concatenate strings and numbers in your Google Sheets:
- Click on the cell where you want to enter the formula.
- Enter the first part of CONCATENATE, which will be
=CONCATENATE( - Now, we will enter the first parameter, which will be used to add the numbers. Here, we will enter
ROW()-1. This is because our range starts from row 2, so by adding the row numbers minus one, we get the value of the position in the list. - Add a comma (
,) to separate the parameters. - Now, we need to add the second parameter, which in this example is the symbol we want to use as a separator. In this example, it is “- “. Remember to include the quotation marks.
- Add a comma (
,) and write the third parameter, which is the cell address containing the first name. In this example, it is A2. - Add another comma (
,) and add a space. Write it as" ". - Add another comma (
,) and write the fifth parameter, which is B2. - Finally, add a closing parenthesis
)to end the formula. - Press Enter to use the formula.
Using JOIN Instead
There are a few cases where using an alternative function may be better than CONCAT or CONCATENATE functions. Using the JOIN function can be a viable alternative if your spreadsheet contains large amounts of data. It allows you to specify a separator character, such as commas and spaces, which can be automatically placed.
The syntax of JOIN is as follows:
=JOIN(delimiter, val1, val2, ...)
The delimiter parameter defines the string or character between the concatenated values. The val parameters define the values to concatenate.
Frequently Asked Questions
If you’re a regular reader, you know my love for breaking down the syntax of a function and answering frequently asked questions. That’s also the case in this guide. Above, I’ve given precise examples of how to concatenate using Google Sheets and when it makes sense to use the CONCAT or JOIN functions instead. Below, I will answer some frequently asked questions about merging cells.
What does CONCATENATE do in Sheets?
The CONCATENATE function combines the values from different cells into one. It helps speed up tasks such as combining first names and last names to create a full name. This function works similarly to the CONCAT function; it’s just more powerful. CONCAT only allows joining up to 2 values, while CONCATENATE allows you to join multiple values.
How to reverse a concatenation?
If you want to split cells, you will use the SPLIT function. Basically, you will use =SPLIT() and enter the cells you want to separate and the delimiter inside the cells that indicates where you want to perform the split. Read my guide for an in-depth analysis.
How to concatenate two columns in Sheets?
To CONCATENATE data from two different columns, click on the cell and enter the first part of CONCATENATE, which is =CONCATENATE(). Click on the cell references for each parameter you want to join, separated by commas. Finally, add a closing parenthesis to end the formula. If you do it in a single cell adjacent to the two columns, you can click and drag the formula down to concatenate two columns into a third. This essentially makes it a array formula.
How to concatenate a range of cells in Sheets?
To use CONCATENATE in Sheets, click and select the cells you want to combine. Then, click on Insert in the top bar, click on Function, then click All. Next, click on CONCATENATE to insert the function into your spreadsheet. You can also add spaces by adding two quotation marks as a parameter and adding a space between the quotation marks.
How to concatenate 3 columns in Sheets?
To use the CONCATENATE formula with three columns, click and select the three cells. Then, click on Insert in the top bar, click on Function, then click All. Next, click on CONCATENATE to insert the function into your spreadsheet. You can use the fill handle to apply the formula throughout the column.
Can Google Sheets CONCATENATE with a comma?
Yes, the CONCATENATE function in Google Sheets works with any separator, including commas. You simply need to include it in quotes between the cell references you want to concatenate. For example, if you want to join cells A1 and B1, you can use the following formula:
=CONCATENATE(A1, ",", B1)
You can also add a space with the comma in the separator, like this:
=CONCATENATE(A1, ", ", B1)
Conclusion of the Google Sheets Concatenation Guide
After following my guide, which includes detailed examples, screenshots, and explanations of relevant functions, you should now be able to perform a multitude of CONCATENATE functions in Google Sheets.
Furthermore, you should be able to discern its advantages over the CONCAT function and understand when to opt for the JOIN function instead. Whether you choose CONCATENATE, CONCAT, or JOIN to merge text from two cells in Google Sheets, feel free to ask for additional assistance. Please leave a comment below if you found this helpful or if you have any additional questions that I did not address.
Don’t forget to check out my website Crawlan.com for more tips and tricks on Google Sheets and other exciting topics!
