Typing text into a Google Sheets cell is easy. But what if you want to edit that text to include more and apply it to multiple cells? You can add text with a formula in several ways.
If you search the web, you’ll see that there are multiple ways to replace, change, or replace text in Google Sheets. But if you just want to add something 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 is available in both Google Sheets and Microsoft Excel as a way to combine text. This function allows you to add text from multiple cells or text strings in any order.
RELATED: Merge 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 ID indicator to the beginning of our order number in cell D2. You would use the following formula:
Note that the text you want to add must be enclosed in 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 in the lower-right corner of the cell with the formula to copy it down.
The feature is flexible enough to add the text in multiple locations within the string. This formula allows you to add ID- to the beginning and -1 to the end of the value in cell D2. Then use the fill handle again for the remaining cells.
For another example, CONCATENATE also lets you add text from other cells. 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 in between. Then use the fill handle to apply the same formula to the rest of the cells.
Add text with the ampersand operator
A function isn’t the only way to add text with a formula in Google Sheets. As an alternative to CONCATENATE, you can use the ampersand operator.
RELATED: 9 Basic Google Sheets Features 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 as CONCATENATE, but for many it’s easier to remember and quicker to insert.
Plus, you can use the fill handle just like any other formula, making copying into additional cells a breeze.
Let’s use the ampersand operator for our second example above, adding 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 that you bet! We use the ampersand to combine the customer’s phone number in cell C2, a space, and the order number in cell D2.
As with CONCATENATE, the space is enclosed in quotes as if it were a word or special character.
Insert text with LEFT, MID and RIGHT
If you want to add text in the middle of a text string within a cell, you can do it with the REGEXREPLACE function to replace the text. However, you need a little knowledge with regular expressions, which have a steep learning curve. Instead, you can use the LEFT, MID, and RIGHT functions with the ampersand operator.
RELATED: Using 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
The syntax for MID is
MID(string, start, length) what you can use text or a cell reference for
string and numbers for the first character and length to be extracted.
To add a hyphen after the first character in our value in cell D2 and keep the rest of the values the same, you can use this formula:
Our string is six characters long. So the
LEFT formula refers to the cell and then to the first character, the ampersand appends the hyphen in quotes, another ampersand appends the end of the string with the cell reference and the remaining five characters using the
With this next formula, we will use all three functions to add hyphens after the first two and before the last two characters in the 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 is truncated with the cell reference, leading character number, and 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 fastest and easiest ways to add new text to existing text in Google Sheets.
For more information about using AND and OR, see Google Sheets.