There are a few things you can do to add text with a formula in Google Sheets. The first is to select the cell you want to add the text to, and then click on the formula button. This will open up a new sheet with the formula you need to use. Next, type in the text you want to add, and then click on the OK button.
Typing text in a Google Sheets cell is simple. But what if you want to edit that text to include more and apply it to multiple cells? Using a couple of different methods, you can add text using a formula.
If you search the web, you’ll find that there are multiple ways to substitute, change, or replace text in Google Sheets. But if you want to simply add to existing text there are a few quick and easy formulas.
Combine Text With the CONCATENATE Function
You may already be familiar with the CONCATENATE function as it’s available in both Google Sheets and Microsoft Excel as a way to combine text. This function lets you add text from multiple cells or strings in any order you like.
RELATED: How to Concatenate Data from Multiple Cells in Google Sheets
The syntax for the function is CONCATENATE(string1, string2, …) where only the first argument is required.
As an example, we want to add the indicator ID- to the beginning of our order number in cell D2. You would use the following formula:
Notice that the text you want to add should be placed within quotes.
Once the function and formula do their job, you can copy the formula to the remaining cells to change the rest of the order numbers. Drag the fill handle on the bottom right corner of the cell containing the formula to copy it down.
The function is flexible enough to append the text in multiple locations within the string. Using this formula, you can add ID- to the beginning and -1 to the end of the value in cell D2. Then again, use the fill handle for the remaining cells.
For one more example, CONCATENATE lets you append text from other cells as well. Here, we want to add our customer’s phone number in cell C2 to the end of their order number in cell D2 with a space between. Then use the fill handle to apply the same formula to the rest of the cells.
Append Text With the Ampersand Operator
A function isn’t the only way to add text using a formula in Google Sheets. As an alternative to CONCATENATE you can use the ampersand operator.
RELATED: 9 Basic Google Sheets Functions You Should Know
We’ll use the same example above to add ID- to the beginning of our order number in cell D2. You would use this formula:
The ampersand in the middle combines the two in the same way that CONCATENATE does but for many is easier to remember and faster to insert.
Plus, you can use the fill handle exactly as you would with any other formula so that copying it to additional cells is a breeze.
Let’s use the ampersand operator for our second example above where we add ID- to the beginning of the order number in cell D2 and -1 to the end. Here’s the formula:
So now you may be wondering, does the ampersand work with multiple cell references and even spaces? The answer is you bet! We’ll use the ampersand to combine the customer’s phone number in cell C2, a space, and the order number in cell D2.
Like with CONCATENATE, the space is placed within quotes as if it was a word or special character.
Insert Text Using LEFT, MID, and RIGHT
If you want to add text to the middle of a text string within a cell, you could do so using the REGEXREPLACE function to replace the text. However, you’ll need a bit of knowledge with regular expressions, which has a steep learning curve. Instead, you can use the LEFT, MID, and RIGHT functions with the ampersand operator.
RELATED: How to Use Regular Expressions (regexes) on Linux
The syntax for LEFT and RIGHT is the same, LEFT(string, characters) where you can use text or a cell reference for the string and a number for characters.
The syntax for MID is MID(string, start, length) where you can use text or a cell reference for string and numbers for the starting character and length to extract.
To add a hyphen after the first character in our value in cell D2 and keep the remaining values the same, you can use this formula:
Our string is six characters long. So the LEFT formula references the cell and then the first character, the ampersand adds the hyphen in quotes, another ampersand adds the end of the string with the cell reference and the remaining five characters using the RIGHT formula.
With this next formula, we’ll use all three functions to add hyphens after the first two and before the last two characters in that same string. You would use this formula:
By inserting the MID function between the other two, we can return the segment in the middle of the string. This part of the formula for MID breaks down with the cell reference, the number of the starting character, and the number of characters.
As with the other formulas, you can use the fill handle to copy down. However, if you have a different number of characters in each string, the results will be skewed. So, keep this in mind.
With multiple ways to combine or edit text in cells, these are the quickest and easiest ways to add new text to existing text in Google Sheets.
For more, look at how to use AND and OR in Google Sheets.
