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.
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.
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.
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.
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.
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.
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) 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
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.
- › How to Use Comparison Operators in Google Sheets
- › AMD’s Ryzen 7000X3D Pricing Looks Really, Really Good
- › Samsung Made a Music Video for the Galaxy S23’s Ringtone
- › Intel’s Arc A750 Graphics Card Has a New Lower Price
- › Windows 10’s Bad Upgrade Prompts Are Back for Windows 11
- › Work and Play Anywhere With the ROG G14 Laptop, Now $400 Off
- › Windows 11 Is Adding Even More Widgets