To manage data, it is important to update it if things change. When working with a text string in Google Sheets, you have several ways to substitute that text without manually editing the string.
Besides Google Sheets’ built-in find and replace tool, you can use a function to substitute text in your spreadsheet, and there are several to accomplish this task. Let’s take a look at the options you have for replacing text in your sheet.
Use Find and Replace in Google Sheets
If you are not comfortable with functions and formulas, this first option is for you. With the Find and Replace function, you can find and replace text easily. It’s also a good option if you want to replace the same text across your entire workbook rather than just one sheet.
LINK: How to find data in Google Sheets with VLOOKUP
Open Edit from the menu and select “Find and Replace”.
HOW TO ADD TEXT WITH A FORMULA IN GOOGLE SHEETS
Find and Replace in the Edit menu
When the dialog box appears, enter the text you want to replace in the Search field and the new text you want in the Replace with field.
Find and Replace text fields
Next to Find, use the drop-down list to select where you want to search and replace. You can choose All Sheets, This Sheet, or Specific Range.
Where to look in Find and Replace
If desired, check the boxes for additional options at the bottom. For example, you can check the Match Case box if you want to find all occurrences of smiTH and replace them with Smith.
Using Find and Replace in Google Sheets
If you want to examine each case before replacing the text, click on “Find”, then on “Replace”. To immediately replace all text, click “Replace All”.
Use the SUBSTITUTE function
Maybe you prefer to use functions and formulas to handle these kinds of tasks in Google Sheets. Or perhaps the existing text has differences in structure or format in several places. With the SUBSTITUTE function, you can replace the text you want and place it in a new cell.
The function syntax is: SUBSTITUTE(current_text, find, new_text, occurrence) where the first three arguments are required. You can use the fourth argument to specify which occurrence in the string to modify if there is more than one.
Select the cell where you want to add the updated formula and text. In this example, we’ll just change Jane Doe’s name in cell A1 to Jane Wilson.
=SUBSTITUTE(A1, “Doe”, “Wilson”)
SUBSTITUTE function for a word
Another example, we want to substitute only a small portion of the text in cell A1. Using this formula, you can change “Iphone” to “iPhone”:
=SUBSTITUTE(A1, “Ip”, “iP”)
SUBSTITUTE function for letters
To show how to use the optional occurrence argument, we want to change 2022 to 2023 in cell A1. The argument uses the numbered occurrence in our string, which is 3. This means that we will change the third digit to 2 that the function finds.
=SUBSTITUTE(A1, »2″, »3″,3)
SUBSTITUTE function for year numbers
To repeat, A1 is the cell containing the text, 2 is the character to replace, 3 in quotes is the character to replace with, and the last 3 is the occurrence.
Use the REPLACE function
Another way to replace text in Google Sheets is to use the REPLACE function. Using this option, you can replace part of a text string with another depending on the position and length to be replaced.
The syntax is REPLACE(current_text, position, length, new text). Only the first three arguments are required, but you’ll want to use all of them.
In this example, we want to replace the ID- at the beginning of our order number with ON- in cell E2.
The 1 in our formula represents the position of the first character we want to replace in the string, and the 2 represents the length of the characters to replace.
REPLACE function for letters
Another example, we want to replace part in the middle of our string in cell A1. Using this formula, you can replace “New Iphone Case” with “New iPhone 13 Case”.
Here, we were able to replace the uppercase “I” in “Iphone” with a lowercase one and add 13, all in the middle of our text string. The 5 is the position of the first character and the 6 is the length of the string to be replaced.
REPLACE function for words
Use the REGEXREPLACE function
Another function you can use to substitute text in Google Sheets is REGEXREPLACE. This is the ideal option for those who are familiar with using regular expressions. Google uses RE2 expressions and offers help in the form of a syntax list.
The function syntax is REGEXREPLACE(current_text, regular_expression, new_text) where the arguments are required.
In this example, we’ll replace our order number in cell A1 using the following formula:
=REGEXREPLACE(A1, »[0-9]+”, “111111″)
Here the [0-9]+ stands for numbers and is enclosed in quotation marks and the 111111 is the new text.
REGEXREPLACE function for digits
For another example of using the REGEXREPLACE function, we replace the spaces in our product number with dashes.
=REGEXREPLACE(A1, “s”, “-” )
Here, the s is the regular expression for spaces. Then we replace them with hyphens (-).
REGEXREPLACE function for spaces
When you need to substitute new text for old, you have more than one way to do it in Google Sheets. Use the one that suits you best!