This article highlights different ways to find and replace text and numbers in MS Excel. It is an important function for correcting errors or making quick changes. This exercise can be time-consuming if you don’t have the right skill set. With proper skills, you can do it in minutes.
3 Methods for finding and replacing text or numbers in Microsoft Excel:
- Use a keyboard shortcut
- Use the Find & Select feature
- Use the SUBSTITUTE formula
Method #1: Use a Keyboard Shortcut
This is one of the most widely used methods for finding and replacing text and numbers in MS Excel. Keyboard shortcuts are great to use, as it allows you to work more efficiently.
Step #1: Open your Excel spreadsheet
Open an Excel file to find and replace text and/or numbers in that workbook.
If you already have an existing workbook in which you want to perform this exercise, then open that.
Step #2: Select the cell
Click on the cell in which you want to find and replace text and/or numbers.
Remember that the cell you have selected must contain text and/or numbers.
If you’ve opened a Blank workbook, first insert some text and/or numbers in the selected cell.
Step #3: Press the keyboard shortcut
Now, press the Ctrl key on your and also press the H key at the same time. This is the ‘Ctrl + H’ shortcut.
The Find and Replace window will appear as shown below.
Step #4: Enter the text to find and replace
In the Find what: box, enter the text and/or number you want to replace.
For example, if you want to replace 2021 with 2022, then you will have to type 2021 here.
In the Replace with: box, enter the text and/or number you want to have in the sentence.
In our example, we typed 2022.
Step #5: Replace
After putting in the desired values, click on the Replace button.
The text will be replaced.
If you have many instances where you need to replace the same text, you will instead select the ‘Replace All button.’
Step #6: Close the window
Once done, you can close the Find and Replace window by clicking on the Close button.
Method #2: Use the Find & Select Feature
This method is very much like the keyboard shortcut method. The only difference here is the use of the toolbar to navigate to the Find & Select feature.
Step #1: Open your Excel spreadsheet
Open an Excel file to find and replace text and/or numbers in that workbook.
If you already have an existing workbook in which you want to perform this exercise, then open that.
Step #2: Select the cell
Select a cell by clicking on it. Here, remember that the cell you have selected must contain text and/or numbers.
If you’ve opened a Blank workbook, first insert some text and/or numbers in the selected cell.
Step #3: Find & select
On the Home tab ribbon, click on the Find & Select icon.
From the dropdown menu that opens, click on the Replace… option.
The Find and Replace window will appear as shown below.
Step #4: Enter text to find and replace
In the Find what: box, enter the text and/or number you want to replace.
For example, if you want to replace 2021 with 2022, then you will have to type 2021 here.
In the Replace with: box, input the text and/or number you want to have in the sentence.
In our example, we typed 2022.
Step #5: Replace
After entering the desired values, click on the Replace button.
The text will be replaced.
If you have many instances where you need to replace the same text, you will instead select the ‘Replace All button.’
Step #6: Close the window
Once done, close the Find and Replace window by clicking on the Close button or clicking on the cross in the top right corner, as shown below.
Method #3: Use the SUBSTITUTE Formula
Use the SUBSTITUTE formula to find and replace text and/or numbers. This is helpful in updating data without changing the original cell.
Step #1: Open your Excel
Open an Excel spreadsheet to find and replace text and/or numbers in that workbook.
If you already have an existing workbook in which you want to perform this exercise, then open that.
Step #2: Select the cell
Select the cell where you will enter the formula by clicking on it.
That cell can be on the same or a different worksheet. It’s D1 in our example.
Step #3: Enter the formula
The syntax for the SUBSTITUTE formula is
=SUBSTITUTE(text, old_text, new_text, [instance_num]). The parameters with their meanings are as follows:
text = text or the reference to a cell containing text in which you want to substitute characters.
old_text = the original text you want to replace. Note that old_text’s case should match with that of the text’s case.
new_text = the text you want to replace the old_text with.
instance_sum = the occurrence of old_text which you want to replace. If omitted, every instance of old_text gets replaced.
The above formula replaces the first instance/occurrence of 2021 in cell A1 with 2022. The parameters in this formula are:
text = A1 (reference to the cell containing the text)
old_text = 2021
new_text = 2022
instance_sum = 1 (to replace only the first instance of 2021)
Step #4: Press Enter
Once you’ve typed the formula, press Enter on your keyboard to see the result.
Conclusion
Is it possible to change all the dates in your annual report in one go? Do you have to find a repetitive piece of text and replace it with other text? These three methods are your solutions.
Even though the above examples show the replacement of numbers, you can also use it to replace text.
To find and replace text in a section of your worksheet, you can either only select a column or a row, or click on an empty cell for the search to go through the whole sheet.