[ad_1]
Important to manage the data is to update it if things change. When you’re working with a string of text in Google Sheets, you have a few ways to replace that text without manually changing the string.
Along with the built-in Find and Replace tool in Google Sheets, you can use a feature to replace 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 on your sheet.
Use Find and Replace in Google Sheets
If you are not comfortable using functions and formulas, this first option is for you. With Find and Replace, you can easily find and replace text. This is also a good option if you want to replace the same text across the entire workbook instead of just one sheet.
RELATED: How to find data in Google Sheets with VLOOKUP
Open Edit from the menu and select “Find and Replace”.
When the dialog box appears, enter the text you want to replace in the Find What field and the new text you want in the Replace With field.
Next to Find, use the dropdown box to select where you want to find and replace. You can choose All Sheets, This Sheet, or Specific Range.
Optionally, check the boxes for additional options at the bottom. For example, you can check Match Case if you want to find all instances of smiTH and replace them with Smith.
If you want to review each case before replacing the text, click “Find” and then “Replace.” To replace all text immediately, click “Replace All”.
Use the SUBSTITUTE function
Maybe you prefer to use functions and formulas to handle tasks like this in Google Sheets. Or perhaps the existing text varies in structure or format in various places. With the REPLACE function, you can replace the text you want and place it in a new cell.
RELATED: Functions vs. Formulas in Microsoft Excel: What’s the Difference?
The syntax of 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 is more than one.
Select the cell where you want to add the formula and updated text. In this example, we will make a simple change from Jane Doe in cell A1 to Jane Wilson.
=SUBSTITUTE(A1,"Doe","Wilson")
As another example, we want to replace only a small portion of the text in cell A1. Using this formula, you can change “Iphone” to “iPhone”:
=SUBSTITUTE(A1,"Ip","iP")
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 we will change the third number 2 that the function finds.
=SUBSTITUTE(A1,"2","3",3)
To reiterate, A1 is the cell with the text, 2 is the character to replace, 3 in quotes is the character to replace, and the last 3 is the occurrence.
Use the REPLACE function
Another way to replace text in Google Sheets is by using the REPLACE function. With this option, you can replace part of a text string with another depending on the position and length to replace.
RELATED: How to find the position of a value 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 all of them.
In this example, we want to replace ID- at the beginning of our order number with ON- in cell E2.
=REPLACE(E2,1,2,"ON")
The 1 in our formula it represents in which position is the first character that we want to replace in the string and the 2 represents the length of the characters to replace.
As another example, we want to replace a part in the middle of our string in cell A1. With 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.
Use the REGEXREPLACE function
One more function you can use to replace text in Google Sheets is REGEXREPLACE. This is the ideal option for those who are familiar with the use of regular expressions. Google uses RE2 expressions and offers help with a list of syntax.
RELATED: How to use regular expressions (regexes) in Linux
The syntax of the function is REGEXREPLACE(current_text, regular_expression, new_text) where the arguments are required.
In this example, we will replace our order number in cell A1 using the following formula:
=REGEXREPLACE(A1,"[0-9]+","111111")
Here the [0-9]+ represents numbers and is placed between quotation marks and the 111111 is the new text.
For one more example using REGEXREPLACE, we are replacing the spaces in our product number with hyphens.
=REGEXREPLACE(A1,"s","-")
Here the s is the regular expression for spaces. Then we replace them with hyphens (-).
When you need to replace new text with old, you have more than one way to do it in Google Sheets. Use what works best for you!
RELATED: How to remove extra spaces in your Google Sheets data
[ad_2]
