Want to combine cells in Google Sheets? Let’s talk about the concatenate function. It combines the contents of multiple cells. Just like the CONCAT function in Excel, there’s also a CONCAT in Google Sheets. Note that it’s different from the newer function, CONCATENATE. They’re different but work in similar ways. I’ll show you how to use both. Read on to learn how to concatenate in Google Sheets.
Table of Contents
What is the Google Sheets Concatenate Strings Function?
In spreadsheet applications like Google Sheets, “concatenate” refers to the process of linking or merging elements in a sequence. Within Sheets, the concatenate function is a valuable tool that lets you combine values from different cells into one.
This proves useful for tasks like quickly creating full names by merging first and last names or consolidating registration numbers. I rely on this feature to streamline data synthesis, ensure accurate and coherent representation within my spreadsheets.
Difference Between CONCATENATE and CONCAT in Sheets
Maybe you’ve heard of the CONCAT function in Excel. It’s super useful, as it combines the contents of two cells. You can also use CONCAT in Google Sheets. It differs from the CONCATENATE function in one major way. You’ll use CONCAT to combine two cells. CONCATENATE allows you to join multiple values. That’s helpful when you want to append the contents of multiple cells at once.
CONCATENATE 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.
Concat in Google Sheets Syntax
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 ampersand (&) operator.
Main Difference between CONCAT and CONCATENATE
The CONCATENATE formula is more complex than the CONCAT formula as it will allow more flexibility if you don’t want 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. That’s super handy when you want to work with names and addresses.
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: Examples
Now that we know how the formula works, let’s look at some examples of CONCATENATE in action. Below, I’ll show you specific examples of how to combine cells using the function. I’ll include screenshots and step-by step instructions.
The Basics: How to Use Google Sheets Concatenate
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:
- Click the cell you want to enter the formula in.
- Enter the starting part of CONCATENATE, which is going to 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 Comma ( , ) to separate the parameters.
- Now, write the second parameter. In this example, it is the B2 cell range.
- Finally, add Closing brackets “ ) ” to finish the formula.
- 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. Note that this can be especially useful when working with names.
For example, you might pull a dataset from the US Census. Provided columns of first and last names, you could combine the names and add spaces between them. Of course, that’s only one reason you may want to add a space when concatenating cells. It’s also a common solution when concatenating dates and times.
Want to see what it looks like? Here’s how to perform a Google spreadsheet concatenate with a separator (with screenshots):
- Click the cell you want to enter the formula in.
- Enter the starting part of CONCATENATE, which is going to 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 Comma ( , ) to separate the parameters.
- Now, write the separator inside 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 Closing brackets “ ) ” to finish the formula.
- 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:
- Click the cell you want to enter the formula in.
- Enter the starting part of CONCATENATE, which is going to be =CONCATENATE(
- 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.
- Add Comma ( , ) to separate the parameters.
- 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.
- Add Comma ( , ) and enter the third parameter, which is the cell address containing first name. In this example, it is A2.
- Add another Comma ( , ) and add a space. Write it as “ ” with quotation marks.
- Add another Comma ( , ) and write the fifth parameter, which is B2.
- Finally, add Closing brackets “ ) ” to finish the formula.
- 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
If you’re a regular reader, you know my love of breaking down a function’s syntax and answering the most common questions. That’s the case in this guide, too. Above, I gave specific examples of how to concatenate using Google Sheets and when it makes sense to use the concat or join functions instead. Below, I’ll answer some common follow-up questions on combining cells.
What Does CONCATENATE Do in Sheets?
The CONCATENATE function combines values from different cells into one. This speeds up tasks like combining first and last names to create a full name. This function works similarly to the CONCAT function. It’s just more powerful. CONCAT only allows you to join up to 2 values, whereas CONCATENATE will enable you to join multiple values.
How Do You Reverse Concatenate?
If you want to split cells, you’ll use the SPLIT function. Basically, you’ll use =SPLIT() and enter the cells you want to separate and the thing inside the cells that shows where you want to make the split. Read my guide for the full breakdown.
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. If you do this in one cell adjacent to both columns, you can click and drag the formula all the way down to concatenate two columns into a third. That essentially makes it an array 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.
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 double 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
Having followed my guide, which includes comprehensive examples, screenshots, and detailed breakdowns of relevant functions, you should now be able execute a multitude of CONCATENATE functions in Google Sheets.
Additionally, 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, don’t hesitate to seek further assistance. Please leave a comment below if you found this helpful or if you have a follow-up question I haven’t addressed.
