Find & Replace – A function that will give you data search and replace
What is the Find & Replace function and its uses?
1. Find data –
Like any application, Ctrl + F is the Shortcut Keys for Find function in Excel. Drop down option of Find & Select is found in the right side in the Home tab of the worksheet, clicking on it will open a new window of Find & Replace as soon as you select the Find option.
Home >> Find & Select >> Find
Shortcut Key – Ctrl + F
In the image below, we have to search for code A-30. There are two tabs in the fund window. In the Find Tab, type A-30 and click on Find Next. Excel will navigate to the first value of A-30 where it matches.
If the value is present more than once, keep clicking on Find Next. One by one the values will become Navigate.
If we want to see all the results of Find in a list, enter the text or value in the Find what Text box in the Find & Replace window and click on the Options >> button.
The extension window will open. After clicking Find All Button there, the complete list of search results will be visible. If the search result is Maximum, you can maximize the window or maximize the size by dragging.
2. Remove Unwanted Data –
To remove the Dash (-) of Column C in the image below, select the corresponding cells or columns and press Ctrl + F and open the window of Find & Replace and click on Replace Tab.You can also use the shortcut Ctrl + H for the option of Replace.Type the value you want to remove in this text box. If you want to remove Dash here type – . Leave the Replace with Text box blank. Replace All Click this button.
Selected cell dashes will be removed. You can also replace or remove any text in this way.
3. Replace Special Character –
4. Replace text or add more text –
Changing, reducing or adding any text of the data can be done with the option of Find & Replace. In the next image, we have to replace VMS Group with VMS Group Pvt Ltd. Press Ctrl + H. Then Type VMS Group in the text box of Find what in the window of Find & Replace or copy paste it. In the text box of Replace With, type the new name VMS Group Pvt Ltd and click on Replace All.
The new name of the company will appear in all the data.
5. Replace numerical data –
Similarly, we can replace and edit any data. To change the value, enter the old value in the Find What Text box and replace with the new value in the Replace window. Your old value will be converted to new value.