Sometimes you ran into a situation where you want to combine or concatenate text or values in different cells, columns & rows in Google Sheets. So the question is how to concatenate in Google Sheets.
If you don’t know the answer then do not worry as we are going to discuss all the concatenate functions in Google Sheets in this blog with complete details and examples.
How to Concatenate in Google Sheets?
In Google Sheets you can achieve the concatenation in two ways, which are as follows:
- Concatenate Using Operator “&”
- Concatenate Using Functions
-
Concatenate Using Operator “&”:
The first way to do concatenation in Google Sheets is by using the concatenate operator which is “&”. E.g. you have data of some customers, but in the data, the name is separated into First Name and Last Name columns. You want to join/combine or concatenate first name and last name.
How to do this?
You can use the concatenate operator as shown below to concatenate text in different cells. Suppose you have data in the following format:
Now let’s use the following formula in cell D2 to concatenate first name and last name.
=B2&” “&C2
As you can see Google Sheets is showing the result of the formula in the tooltip, which can help you format the string according to your requirement.
You might also have noticed that we have added “ “ using 2 & operators. The purpose of this extra text space “ “ was to add space between the first name and last name. If we had to concatenate the first name and last name without it then the formula would be
=B2&C2
And the result would have been AlexDavid. Which does not appear proper.
Now the question is when to use the concatenate operator in Google Sheets.
When to use Concatenate Operator in Google Sheets?
You can use Concatenate Operator when you want to join, combine or concatenate text in a few cells. E.g. in our case, we just wanted to combine the text of 2 cells only. But if you have a large number of cells to combine it’s better to use concatenate functions instead of operators.
-
Concatenation Using Function in Google Sheets:
Concatenate using function is almost the same as Concatenate using the operator. The only difference is you can use a concatenate function on a large number of cells. Let us show you how this function works.
Let’s say we have the same data set that is shown in the above example. Now you want to concatenate the first and last name using the concatenate function. You can enter the following formula in cell D2 to concatenate the first and last names.
=CONCATENATE(B2,” “,C2)
You can see the above-mentioned formula in action in the image below:
Google Sheets shows you all the instructions and tooltips below the cell while you can also see the resultant string in the tooltip above the cell. After entering the formula Google Sheets will show you the option to auto-fill the rest of the cells below with the same formhttps://spreadsheetsexpert.com/google-sheets/how-to-concatenate-in-google-sheetsula as shown in the image below. Click on the “tick” symbol to accept the changes.
There is one more function in Google Sheets which is used to concatenate two values and that is CONCAT. Let’s see how it works.
-
Concatenate Using CONCAT function:
In Google Sheets, there is one more function that is used for concatenation. But the issue with this function is that you can only use it for 2 values only. You cannot pass more than 2 strings/values to it.
The syntax of this function is as follows:
=CONCAT(B2,C2)
You can see the formula in working condition in the image below.
Now you have learned how to concatenate in Google Sheets.
With over two decades of experience in writing about Microsoft Excel, Google Sheets, and various other spreadsheet tools, Muhammad Nadeem Salam is your go-to expert for all things data. Since 2004, he has been passionately sharing his knowledge and insights through engaging and informative blog posts, helping countless readers unlock the full potential of their spreadsheet tools.
