Show 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 FunctionYou 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 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: =CONCATENATE("ID-",D2) 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. =CONCATENATE("ID-",D2,"-1") 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. =CONCATENATE(D2," ",C2) Append Text With the Ampersand OperatorA 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: ="ID-"&D2 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: ="ID-"&D2&"-1" 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. =D2&" "&C2 Like with CONCATENATE, the space is placed within quotes as if it was a word or special character. Insert Text Using LEFT, MID, and RIGHTIf 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, The syntax for MID is 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: =LEFT(D2,1)&"-"&RIGHT(D2,5) Our string is six characters long. So 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: =LEFT(D2,2)&"-"&MID(D2,3,2)&"-"&RIGHT(D2,2) By inserting the 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. READ NEXT
How do I add text and numbers in Google Sheets?The CONCATENATE() function provides the same functionality as the concatenation operator (&) to add text to a formula in Google Sheets. The only difference is in the way both are used. The general syntax for the CONCATENATE Google Sheets text and formula in same cell function is: =CONCATENATE(text1, [text2],…)
How do I edit a Google sheet?Edit data in a cell. Open a spreadsheet in the Google Sheets app.. In your spreadsheet, double-tap the cell you want to edit.. Enter your data.. Optional: To format text, touch and hold the text, then choose an option.. When done, tap Done .. |