The Excel REPLACE function substitutes another text string for the characters in a given text string that are specified by location. To search for something in your workbooks, such as a certain number or text string, use Excel's Find and Replace tools. You have two options: either find the search item for future use or swap it out for something else. In your search words, you can use wildcard symbols like question marks, tildes, asterisks, or digits. You can perform searches using rows and columns, comments or values, worksheets, or entire workbooks.

Replace Text in Excel Formula Manually

In this case, we utilized a formula with the IF function and the result was Yes for prices larger than 100. Now, we want to manually swap out Yes for Greater than 100 in the formula.

1.   Choose whether or not to select the column's first cell that has a value greater than 100.

Consequently, the formula for this cell is displayed in the formula bar.

2.  Manually change Yes to Greater than 100 in the formula bar.

3.   Press ENTER and pull the Fill Handle tool downward.

Result:

This will allow you to change Yes to Greater than 100 in the formula.

Employing Replace Excel Formula with Text Replacement Option

In this section, the formula for the >100 or not column will have the text Yes replaced with Greater than 100 using the Replace option.

1.   Click the Home tab, and select the Find and Replace Option.

2.   As an alternative to this method, you can also utilize the shortcut key CTRL+H.

The Find and Replace dialog box will then show up.

1.   Select and write the following.

Find what → Yes
Replace with → Greater than 100
Within → Sheet
Search → By Rows
Look in → Formulas

Choose to Replace All from the menu.

Then a message box stating All done. will display. 9 replacements were made.”

Using REPLACE Function with a Condition in a Cell

The REPLACE function is used to supersede a certain number of letters from a word and form a new word in Excel. In this section, we will discuss how this REPLACE functions with conditions.

We assume that some of our data in the dataset are wrong. We will correct that data using the Replace function.

1.   Here, we add a new column named Modification in the dataset.

2.   We identified that the data of Cell B9 is wrong. We will update the modified data on Cell E9.

3.   Write the formula given below in Cell E9:

=REPLACE(B9,1,5,Blue)

4.  Then, press Enter.

5.   We get the replaced data by applying the REPLACE function in Cell E9.

Did you learn about how to replace text in excel? You can follow WPS Academy to learn more features of Word Document, Excel Spreadsheets, and PowerPoint Slides. You can also download WPS Office to edit word documents, excel, and PowerPoint for free of cost. Download now! And get an enjoyable and easy working experience.