In some previous posts, I have talked that how to use the REPLACE function or Substitute functions to replace text string. And this post will guide you how to use Find and Replace tool to replace text from a worksheet in excel.
The Find and Replace tool is a very useful tool to replace the text string or characters with new text or character in excel. It can change the entire contents of a cell and also can search the content from a specific worksheet or the whole workbook. You can choose its search range while searching.
For example: if you want to change the whole worksheet that contains “word” text to “excel”. Please refer to the following steps:
1# click on the “Find& Select” button on the Editing group under Home tab.
2# select the Replace tab, then “Find and Replace” window will appear.
3# enter “word” in the text box of “Find what:”
4# enter “excel” in the text box of “Replace with:”
5# you can also click on the “Format” button to format the content that you are replacing. Such as: you can select “Text” under “Number” tab to format the content as text.
6# click Replace All. All of text that is searched will be replaced at once.
7# at last, the tool will tell you how many replacements were made, see below screenshot.
The find and Replace tools is also allow to use the wildcards (* , ?) in the Find what field. Such as, replace any cells that starts with the character “e”, then we can enter “e*” into the text box of “Find what”.
More reading: REPLACE function in excel