Find and Replace option in Excel makes our daily work very easy and It saves a lot of time. For example, if we have to find any text or number in our data and it has to be replaced with some other text or number then it can be done by Find and Replace option in very easy way.
Find and Replace option is available in Home tab>>Find and Select in Editing group.
Shortcut keys: Ctrl+F (for find) and Ctrl+H (for Replace)
Find:
Find window will be opened on pressing Ctrl+F. Below given option is available in find window.
Find What: In the find what box type the text or number which you want to find in the worksheet or selected range.
Find All: You can find the all text available in the worksheet on selected range which was given in Find What box.
Below are the box and buttons are available in Find window
Find Next: You will be able to find one by one by using Find Next option.
Options: There is an Options button available in find window. If you will click on this button, find window will look like below image.
Below given features are visible after clicking on options button
Within: In this option you can choose where you want to find in sheet or in entire workbook.
Search: In this option you choose the search type like- by column or by row.
Look in: In this option you choose where do you want to search your text like- in Formulas, in Values or in Comments
Match case: This is a check box, if you tick this option then your search will be case sensitive. For example- if you are searching “agent” then it will not search “Agent”
Match entire cell contents: This is a check box, if you tick this option then you will be able search the complete contents of the cells. For example- if you are searching “age” then it will not search “Agent”
Format: By using the format option you can find you text or number only for the particular format.
Replace:
By using the Replace option you can replace you text or number with another.
In Replace window all the option will remain same as Find window only Replace With box, Replace All button and Replace button are extra available.
Wild Cards: You can use wild card in Find and Replace option.
Let’s say we have the “NAME – EMP ID” column wherein Name and employee id in a single column separated by “ – “.
If we have to keep only Name and remove the EMP ID then we can do it by Replace option.
- Select the range on column A.
- Press Ctrl+H to open Replace window
- Put “ -*” in Find What box.
- Put “” (Blank) in Replace with box.
- Click on Replace All button.
- All the employee ids will be replaced with the blanks and only Name will available in column A.
If we have to keep only Emp ID and remove the Name then we can do it by Replace option.
- Select the range on column A.
- Press Ctrl+H to open Replace window
- Put “* -” in Find What box.
- Put “” (Blank) in Replace with box.
- Click on Replace All button.
- All the names will be replaced with the blanks and only EMP ID will available in column A.
