Mehrab Imtiaz
Concatenating values is one of the more fundamental tasks performed in a spreadsheet. Today, we will be specifically looking into how to concatenate strings in Google Sheets.
Let’s get started.
2 Approaches to Concatenate Strings in Google Sheets
1. Using the Ampersand (&) to Concatenate Strings
The Ampersand (&) operator is the classic yet simplest way to concatenate two cells that contain strings.
To show this method, we have created the following dataset:
Here we have a bunch of Phone Numbers and a Region Code. Our task is to concatenate the two strings into one.
The formula with ampersand is:
="+088"&B3
We have done two things over here:
- Inputted the Region code as a string (enclosed in “”).
- Use an ampersand (&) to concatenate the number with a cell reference to cell B3.
Now, we can leave it as it is, but the result doesn’t look presentable yet. We must add a delimiter between the Region code and the Phone Number.
A delimiter can be whitespaces, commas (,), hyphens (-), or anything your spreadsheet requires. We will be using a whitespace for our results.
Our updated formula:
=$C$3&" "&B3
The whitespace delimiter (“ “) is concatenated between the Phone number and Region code with ampersands. And we have also updated the reference to the Region code with an absolute cell reference. This allows our formula to reference this particular cell for the Region code whenever we use the fill handle.
Taking Advantage of ARRAYFORMULA
There are a lot of ways we can transform the base formula that we have just discussed to suit our needs or understanding. On top of that, we can take advantage of some built-in functions to get a lot of work done in a short amount of time.
We saw in our dataset that we have a list of phone numbers that we want to concatenate with a region code. What if this list of numbers was larger? Maybe hundreds of entries?
In such cases, we can use the ARRAYFORMULA to present a list of outputs with one single formula:
=ARRAYFORMULA($C$3&" "&B3:B7)
As you have noticed, the formula within the ARRAYFORMULA function is similar to what we have discussed in the previous section. The only difference is that this time instead of a cell reference for the Phone number (B3) we have used a range reference (B3:B7).
With the help of the ARRAYFORMULA function, we can present the entire range in a single go.
Tip:
You can alternatively apply an array output by simply writing the formula, =$C$3&” “&B3:B7, and instead of pressing ENTER, press CTRL+SHIFT+ENTER. This will automatically enclose the formula inside the ARRAYFORMULA function.
2. Using Built-In Functions to Concatenate Strings in Google Sheets
Google Sheets now provides us with two built-in functions created just for the act of concatenation. These are the CONCATENATE function and the newer and simpler CONCAT function.
The CONCATENATE Function
The CONCATENATE function syntax:
CONCATENATE(string1, [string2, ...])
With delimiters, it will be:
CONCATENATE(string1, “delimiter”, [string2], “delimiter”, [string3,...])We will once again use our dataset to concatenate the two strings from the Phone number and Region Records columns in Google Sheets.
Our formula:
=CONCATENATE($C$3," ",B3)
And that’s it! That is how simple it is to concatenate strings with the CONCATENATE function on Google Sheets.
The CONCAT Function
Where the CONCATENATE function can take any number of strings, the newer CONCAT function can only take two values to concatenate.
CONCAT function syntax:
CONCAT(value1, value2)
Applying it to our dataset, we get:
=CONCAT($C$3,B3)
As you can see, we cannot apply a delimiter directly into the function since it only takes a couple of values. However, we can apply the ampersand (&) concatenation method that we have learned at the beginning of this article here to include delimiters.
Our updated formula:
=CONCAT($C$3&" ",B3)
From this example, you can see how versatile the ampersand operator is for concatenating not only strings but values, in Google Sheets.
And also that CONCAT is a smaller function and is meant to be used for efficiency when there are only two values to concatenate
Read More: How to Concatenate Text and Formula in Google Sheets (7 Ways)
Final Words
That concludes all the ways we can concatenate strings in Google Sheets. We hope that the approaches we have discussed come in handy for your daily spreadsheet tasks.
Feel free to leave any queries or advice you might have in the comments section below.
Related Articles
- How to Concatenate Values for IF Condition in Google Sheets
- How to Concatenate Two Columns in Google Sheets
- How to Get Opposite of Concatenate in Google Sheets (2 Ways)
- How to Concatenate Double Quotes in Google Sheets (3 Ways)
- Google Sheets QUERY Function to Concatenate Two Columns
- How to Concatenate Strings with Separator in Google Sheets
