So, you have a column in Google Sheets. But, it needs some modification.
How do you append text in Google Sheets?
There may come a time when you need to add text to an existing column in Google Sheets. If you have a lot of data in your sheet, you won’t want to go row by row.
Let’s take a look at the Google Sheets append text functionality, how it works, and when you would use it.
What Does It Mean to Append Text?
Appending text refers to when you add text at the end of an already existing value. So, for instance, you might have:
New York City
But you might want it to say:
New York City (New York)
If you wanted to add the state onto the city, you would need to append the text “ (New York)”. Note that there’s a space there, because the space is formatting and would need to be appended, too. Otherwise, you would get:
New York City(New York)
The regular way to append text would be to go in and edit it. But that’s not always possible. What if you need something that looks like this:
New York City New York New York City, New York
In other words, you might need to preserve two values while also having a value that includes both of them. Whatever the case, you can easily append text simply by adding it on through the formula.
How Do You Use the Google Sheets Append Text Function?
We’re going to start with a sheet of values and currencies. In one column, we have the amounts that we’re recording. But the other column has the actual currency. Neither text alone really describes the price value — because you need both.
So, we have 344 yen, 4 British pounds, 45 US dollars, and so forth. We want to create a cell that includes both of these columns.
First, we will use the CONCAT function. CONCAT is short for “CONCATENATE” which means that you add one string onto another string. It’s the simplest, easiest function to use when you’re appending text — and the clearest.
Now, you might notice that this requires the “Currency” cell. What if you just wanted to add an arbitrary string? You could do that, too.
But there’s a problem here. Because there’s no space (remember how we mentioned formatting?) you’re getting the wrong result. It’s “344Yen” not “344 Yen.”
When you concatenate, you always need to pay special attention to formatting. If you’re not paying attention to your formatting, you may produce something that’s technically correct but doesn’t look right.
How do we fix this? Well, the CONCATENATE function in full allows you to add multiple strings together. So, we will try this:
You could include anything in that second input. But here, it’ll lead to the result that we’re looking for.
Likewise, you could have written:
CONCAT(Column_1, “ Yen”)
It’s important that the string you’re adding be included in quotes. But beyond that, you can put in pretty much anything you want and that text is going to be appended. If you were just trying to add “ Yen” to every column, you might do it this way.
The reason we don’t do it this way in our scenario is because we have different values that we want to add to each amount.
How to Add Text After Formula in Google Sheets
Okay, what if we want to add text after a formula? After all, Google Sheets is meant for formulas.
Let’s expand our original example a little. Now we have a given number of orders for each amount. We need to multiply the amount by the orders to get the total. And we want to display the total with the right currency.
In the above example, we would want the total to read 2,064 Yen. So, that means we need to do a formula or a calculation before we add the string.
So, we type:
CONCAT(column*column2, “ “,column3)
This gives us the result that we want. And we can go down the entire column and it will automatically calculate everything for you:
That’s an easy way to append text to each cell in a column in Google Sheets — even if there’s a formula involved.
At this point, you can probably start to see how powerful concatenation really is. Because you’re able to add together multiple cells, you can put together extremely descriptive text. You could put together a text that includes dozens of columns if you really wanted to!
Using the CONCAT Operator Instead of the Concatenate Function
We often use the functions because it makes things a little clearer for us. But if you want a more advanced method of appending text, you can also use the “&” operator. The “&” operator essentially concatenates anything that follows it.
So, we could type:
Column_1*Column_2 & “ “ & Column_3
And we get the same result!
Now, why didn’t we do it this way to begin with? Obviously, this is the easier way. You can always append more text by using the “&” operator.
The issue is readability; if you don’t understand what it’s doing, you might not realize that it is adding a space or adding text.
CONCAT and CONCATENATE are good functions because they tell someone looking at your file exactly what operations you’re doing.
But if you’re an advanced user, and your sheet will only be used by advanced users, using the & operator is a lot more swift and versatile.
Continuing the Concatenation
At this point, you may realize that CONCAT, CONCATENATE, and even the & operator works for everything. While this is specifically about appending text, you can also use these functions to append numbers, calculations, and other formulas and functions entirely.
Essentially, these functions just tell Google Sheets that you’re not done displaying results; that you need to display the results of whatever comes next, too. So, you can get some pretty complicated results by using concatenation.
Concatenation is also a big part of other markup and programming languages. If you weren’t a programmer before, you are now! You’ll find concatenation appearing in programming languages such as Java, C, and Python.
You now know several ways to append text in Google Sheets. We hope this tutorial was clear and useful for you!
Want to learn more Google Sheets tricks? Check out some of these other articles: