In this article, we will look at a few scenarios of how to append text and other values in Google Sheets.
While most of the methods call for the use of simple formulas, certain user requirements may lead to the customization of these. So, it is better to understand first what to append before how to do so.
Let’s begin by asking a simple question.
What Does Appending Text Really Mean?
Appending text simply means combining two or more text values together in a single cell.
Consider the scenario where we have a cell in Google Sheets containing the first name “John” and another cell containing the last name “Doe”.
We want to append these two text values together to get the full name:
While it looks simple, there are points to consider:
- What will be the position of the appending text (before or after)?
- Do we have to include separators (like commas or whitespace)?
These are some of the questions that we will look at as we discuss the methods in this article.
Note also that this process can also go by another name: concatenation. You’ll see the importance of this word in our methods.
How to Append Text Before or After Another in Google Sheets
Primarily, we must use functions to append text in Google Sheets. And the function in question is CONCAT.
CONCAT function syntax:
The CONCAT function takes two values and combines them together in a single cell.
We want to append the text “ID-“ before these numbers. Let’s see how it’s done.
Step 1: Open the CONCAT function in an empty cell. In the value_1 field, type in “ID-“.
Step 2: After a comma (,) reference the cell that contains the ID.
You should be able to see a preview of the result.
Step 3: Close parentheses and press ENTER. Apply the formula to the rest of the column by dragging the fill handle.
If you want to append a value after the ID number, simply refer to the number in value_1 and ID in value_2:
Also, both append texts can be input as cell references. It’s only the positions of the two texts that matter for the outcome.
As you may have already noticed, the CONCAT function can combine only two values.
This can be quite disadvantageous in many situations. Take the previous example, for instance, both of the value fields are covered with cell references and the function cannot take a third value to act as a separator. So, the result looks unappealing.
If you are looking to append more than two values and/or include separators, it is best to use the older brother, the CONCATENATE function.
Append More Than Two Text or Values at Once in Google Sheets
The CONCATENATE function syntax:
CONCATENATE(string1, [string2, ...])
As you can see, the CONCATENATE function can take an infinite number of text strings and combine them together, allowing users to customize their append results in various ways.
For example, adding whitespace or a comma separator in between the texts we are looking to append in Google Sheets:
We can also use the CONCATENATE function to add symbols and other characters to the final result at any position.
For example, let’s add parentheses around the code and append the values in the columns in Google Sheets::
Using the Concatenate Operator (&) to Append Text in Google Sheets
Google Sheets provides us with an alternative way to append multiple text values together without the use of a function. That is by using the concatenate operator or the ampersand (&) symbol.
Simply input the operator in between the different values as a formula.
For example, let’s append the values of the two columns seen in the previous dataset in the same way, including the parentheses. The formula is:
The concatenate operator (&) simply acts as the connector between the different values.
- How to Concatenate Strings in Google Sheets (2 Easy Ways)
- Concatenate Number and String in Google Sheets
- How to Concatenate Multiple Cells in Google Sheets (11 Examples)
Concatenate or Combine Formula and Text in Google Sheets
Appending values in Google Sheets is not limited to default numbers and text, we can also include formulas and functions in them.
Consider the following worksheet where we have three columns of data. We want to use these values to generate a comment showing the remaining products in the stock of each region code.
First, to find the remaining amount, we can use simple subtraction like:
Or we can utilize the MINUS function:
Whichever method you use, you can put it inside one of the string fields of the CONCATENATE function.
Next, append a little text to add some meaning to the comment.
Finally, finish it off with the respective Region Code.
Close parentheses and apply the formula to the rest of the column:
=CONCATENATE(MINUS(B3,C3)," units remaining in ",D3)
Note: This scenario can also work with the concatenate operator.
That concludes all the ways we can use to append text and other values in Google Sheets. Things are quite simple thanks to some great functions that are available in the application by default, like CONCATENATE.
Feel free to leave any queries or advice you might have in the comments section below.
- How to Concatenate If Cell Is Not Blank in Google Sheets (7 Ways)
- Add Space with CONCATENATE in Google Sheets
- How to Concatenate Strings with Separator in Google Sheets
- Google Sheets QUERY Function to Concatenate Two Columns
- How to Concatenate Double Quotes in Google Sheets (3 Ways)
- Get Opposite of Concatenate in Google Sheets (2 Ways)
- How to Concatenate Values for IF Condition in Google Sheets