It's important that when a customer's name is misspelled or a business changes its name that you alter all your records accordingly. You do this to ensure your business data is accurate and you do it to provide quality customer service to your customers. When you need to make changes like this the process can be cumbersome or relatively easy depending on the tool you use. AbleBits's Advanced Find and Replace for Excel can help you do the task both fast and accurately and it works much better than Excel's built in tool.
Problems with the Excel tool
If you use Excel's built in Find and Replace tool for anything other than a simple change the process will be cumbersome as the tool only works with one worksheet at a time. Worse still, the Excel dialog exhibits some unexpected behavior so you could end up not making all the changes you need to make or worse still, changing data you didn't want to change.
There are other disadvantages of using the Excel Find and Replace dialog – it requires you to click the Find Next button to see each instance of a match before clicking the Replace button to replace that instance. If you click Find All, you’ll be given a list of found items but only those in the current workbook.
Having found all matches for your search text you can replace only one item or all of them. You cannot select only those items you want to replace and perform the replacement on them even though the dialog looks like you can do this. Unwary users could make a disastrous mistake here and click Replace which will change only one item even if multiple matches are selected. And worse still, if the user clicks Replace All then all matches, selected or not selected will be changed.
Enter Advanced Find and Replace for Excel
Advanced Find and Replace for Microsoft Excel is an add-in that will solve all the problems with Excel's own Find and Replace tool. It is from AbleBits.com and it works with Excel 2010 (both the 32 & 64 bit versions), Excel 2007 and Excel 2003 with SP3 installed. It is compatible with Windows 2000, XP, 2003, Vista and Windows 7.
Once installed Find and Replace appears on a new AbleBits.com tab in Excel 2010 and Excel 2007 and in Excel 2003 it appears on the menu bar to the right of Help.
To get started, open the worksheets you want to search – the add-in can search all or a subset of open workbooks. Click AbleBits.com > Find and Replace and a panel appears down the left of the screen. At the top is the box in which you type your search text. Then select where to look for it from the tree list of open workbooks below. Select Check All to check all worksheets or Uncheck All to uncheck all of them. You can also selectively check worksheets to search or click Active Sheet in the Within options to search only the active sheet in the current workbook.
Advanced Find and Replace installs as a tab in Excel 2010 and Excel 2007 and runs from a panel on the left of the screen.
From the Look In options you can select to look for a match in a Value, Formula, Comment or Hyperlink or all of these and you can select Match Case and/or Entire Cell to limit the matches. From the Shortcut options you can set a shortcut to launch the add-in – by default it uses the same Ctrl + F shortcut as the built-in Excel tool or you can use Ctrl + Alt + F instead or disable the shortcut key option.
Once you have configured what to search for and where, click Find All to find all matches. There is one gotcha for this and that is that you should not have a range selected when you click Find All – if you have a range selected, then only that range will be searched.
Any matches found appear in the Search Results panel as a tree view of workbooks containing matches. You can click to disclose the details of the sheet and cells which contain matches. If you click on one of the matches in this tree view, you will go immediately to that location in that sheet in that workbook.
Matches appear in a tree view in the panel and you can click a match to go to it.
To replace the found text with something else, type the Replace With value, select the locations for the text to replace and click Replace All. Only the matches in the selected locations will be replaced as you would expect to be the case. To replace only one instance, select it and click Replace.
Advanced Find and Replace lets you export the matches that you have found. Click the down pointing arrow alongside the Export All option and you can export either the rows with all the found entries or the columns containing them or you can export the highlighted entries or the rows or columns containing the highlighted (selected) entries. The data will be exported to a new workbook containing a new sheet for each sheet that contains a matching entry – this lets you aggregate matching data from multiple worksheets into one workbook for further analysis.
There is a similar option for selecting rows or columns containing matching data.
You can export matches to a new workbook for further analysis.
Help on hand
The Advanced Find and Replace comes with a comprehensive help file which you can access by clicking the Help button at the foot of the panel. Here too you can email support if you have an issue or access the developer website by clicking the company logo.
The add-in includes a detailed Help file accessible via an icon at the foot of the add-in panel.
Advanced Find and Replace will be a timesaver for anyone who regularly has to locate data in a large numbers of worksheets. It lets you not only make changes to the data you find and to do this in an intelligent way, but also extract the data to a new workbook for further analysis.