Important to managing data is updating it if things change. When you’re working with a text string in Google Sheets, you have a few ways to substitute that text without manually changing the string.

Along with the built-in Find and Replace tool in Google Sheets, you can use a function to substitute text in your spreadsheet, and there’s more than one to get the job done. Let’s take a look at your options for replacing text in your sheet.

Use Find and Replace in Google Sheets

If you’re uncomfortable using functions and formulas, this first option is for you. With Find and Replace, you can search for and substitute text easily. This is also a good option if you want to replace the same text throughout your entire workbook rather than just one sheet.

RELATED: How to Find Data in Google Sheets with VLOOKUP

Open Edit in the menu and select “Find and Replace.”

Find and Replace in the Edit menu

When the dialog box appears, enter the text you want to replace in the Find field and the new text you want in the Replace With field.

Find and Replace text fields

Advertisement

Next to Search, use the drop-down box to select where you want to find and replace. You can choose All Sheets, This Sheet, or Specific Range.

Where to look in Find and Replace

Optionally, mark the boxes for the additional options at the bottom. For example, you can mark Match Case if you want to find all instances of smiTH and replace them with Smith.

Use Find and Replace in Google Sheets

If you want to review each case before you replace the text, click “Find” and then “Replace.” To substitute all text immediately, click “Replace All.”

Use the SUBSTITUTE Function

Maybe you prefer using functions and formulas to handle tasks like this in Google Sheets. Or perhaps the existing text varies in structure or format in multiple places. With the SUBSTITUTE function, you can replace the text you want and put it in a new cell.

RELATED: Functions vs. Formulas in Microsoft Excel: What's the Difference?

The syntax for the function 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 change if there’s more than one.

Select the cell where you want to add the formula and the updated text. In this example, we’ll make a simple change from Jane Doe in cell A1 to Jane Wilson.

=SUBSTITUTE(A1,"Doe","Wilson")

SUBSTITUTE function for a word

Advertisement

As 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. That means that we’ll change the third number 2 that the function finds.

=SUBSTITUTE(A1,"2","3",3)

SUBSTITUTE function for numbers in a year

To reiterate, A1 is the cell with 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 substitute text in Google Sheets is using the REPLACE function. Using this option, you can replace part of a text string with another based on the position and length to replace.

RELATED: How to Find a Value's Position With MATCH in Microsoft Excel

The syntax is REPLACE(current_text, position, length, new_text) where only the first three arguments are required, but you’ll want to use them all.

In this example, we want to replace the ID- at the beginning of our order number with ON- in cell E2.

=REPLACE(E2,1,2,"ON")
Advertisement

The 1 in our formula represents which position the first character we want to replace is in the string and the 2 represents the length of the characters to replace.

REPLACE function for letters

As another example, we want to replace a portion in the middle of our string in cell A1. Using this formula, you can replace “New Iphone Case” with “New iPhone 13 Case”.

=REPLACE(A1,5,6,"iPhone 13")

Here we were able to replace the uppercase “I” in “Iphone” with lowercase 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 replace.

REPLACE function for words

Use the REGEXREPLACE Function

One more function you can use to substitute text in Google Sheets is REGEXREPLACE. This is the ideal option for those familiar with using regular expressions. Google uses RE2 expressions and offers help with a syntax list.

RELATED: How to Use Regular Expressions (regexes) on Linux

The syntax for the function 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")
Advertisement

Here, the [0-9]+ represents numbers and is placed within quotes and the 111111 is the new text.

REGEXREPLACE function for numbers

For one more example using REGEXREPLACE, we’re replacing the spaces in our product number with hyphens.

=REGEXREPLACE(A1,"\s","-")

Here, the \s is the regular expression for the spaces. Then we replace those 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 whichever works best for you!

RELATED: How to Remove Extra Spaces in Your Google Sheets Data

Profile Photo for Sandy Writtenhouse Sandy Writtenhouse
With her B.S. in Information Technology, Sandy worked for many years in the IT industry as a Project Manager, Department Manager, and PMO Lead. She learned how technology can enrich both professional and personal lives by using the right tools. And, she has shared those suggestions and how-tos on many websites over time. With thousands of articles under her belt, Sandy strives to help others use technology to their advantage.
Read Full Bio »