Excel Tips Tutorial: How to Find and Replace Values in Microsoft Excel
For example, you have an Excel workbook with a worksheet where you have some information in various columns. At a moment while working you have found that you need to find all Zip codes starting with “20” and replace those with “19”. Let’s see how to do this.
Say, you have the following table of company addresses. Here in last column “ZIP Code” , there are some entries having codes started with 20. Now to find which rows are having wrong ZIP code, first select the column “ZIP Code” by clicking on the column number “E” for this case. Now click on “Find & Select” button in the submenu of “Home” title bar. From drop down menu select “Find” option. You will see that a search box will appear like in below picture - 1. Now put 20 in “Find what” field and click “Find Next” and the first ZIP Code started with 20 will be selected. Now click “Find Next” button again & again to see all the cells with wrong code. You can also get all rows at a time by clicking on “Find All” and all rows will be listed below the search box.
Thus you can find cells which are having different values as per your requirement.
Now, how to replace all ZIP codes started with 20 to codes which will start with 19. First, mark all cells red which are having 20 at start. Check if there is any cell having 20 inside the number. If no, then click again the “Find & Select” button in the submenu of “Home” title bar.
Select “Replace” from the drop down menu and “Find and Replace” search box appears. Now put 20 in the “Find what” field and 19 in the “Replace with” field and click “Replace All” ; you will see that all ZIP Codes started with 20 are replaced with Codes started with 19. (see picture 3)