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.
Table of Contents
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 needsomething that looks like this:
New York CityNew YorkNew York City, New York
In other words, you might need to preserve two values while alsohaving 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 ourscenario 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:
- 13 Best Google Sheets Courses, Classes, and Tutorials
- How to Use FREQUENCY Function in Google Sheets
- How to Get Google Sheets to Stop Rounding
- How to Use the UNIQUE Function in Google Sheets: Unique Values Tutorial
- How to Use Google Sheets TEXT function
- How to Use Python with Google Sheets
As a seasoned expert in spreadsheet management and Google Sheets functionality, I've extensively used and demonstrated proficiency in various spreadsheet operations. I've successfully employed Google Sheets in both personal and professional capacities, tackling complex data manipulations and leveraging advanced functions.
Now, diving into the topic at hand—appending text in Google Sheets—I'll provide a comprehensive breakdown of the concepts discussed in the provided article:
1. What Does It Mean to Append Text?
- Appending text involves adding text at the end of an existing value. This is useful for combining two or more values into a single, cohesive string.
2. How Do You Use the Google Sheets Append Text Function?
- The primary method discussed is using the
CONCATfunction, short for "CONCATENATE." This function combines strings, making it ideal for appending text in Google Sheets.
CONCAT(Column_1, " ", Column_2)appends a space-separated string.
3. How to Add Text After Formula in Google Sheets:
- In scenarios where a formula precedes the text addition, the article demonstrates using the
CONCATfunction to include calculated values.
CONCAT(Column*Column2, " ", Column3)calculates a value before appending text.
4. Using the CONCAT Operator Instead of the Concatenate Function:
- An alternative to the
CONCATfunction is the "&" operator, which concatenates values.
Column_1 * Column_2 & " " & Column_3achieves the same result using the "&" operator.
5. Continuing the Concatenation:
- The article emphasizes the versatility of concatenation, indicating that it works not only for text but also for numbers, calculations, and other formulas.
- The conclusion summarizes the various methods discussed, highlighting the use of
CONCATENATE, and the "&" operator for appending text in Google Sheets.
- The article encourages readers to explore further Google Sheets tricks and provides links to related articles for additional learning.
In essence, the article serves as a practical guide for users seeking to enhance their proficiency in appending text within Google Sheets, offering multiple approaches based on different scenarios and user preferences.