Sep
7

How to find what you need in Excel: cell values, formulas, hyperlinks?

It’s well known that the art of reaching the goal is in many respects based on the skill of setting the correct goal. There is no room here for abstract plans and tasks. As well as the biggest buildings and highest sky-scrapers consist of small blocks, truly immense tasks consist of elementary actions and simple steps. Do you recall making construction models when a child, when from diminutive details you could make everything you wanted? Nothing has changed since then, and looking at the final result of your work you should always keep in mind that it resulted from completing elementary tasks each of which is highly simple and doesn’t require any super-efforts or extra time.

And when instead of the colossus you can see just a chain of elementary operations ahead, new requirements will come to the forefront. Namely, comfort and easiness-to-use when working with your familiar tools. In our case it is the Microsoft Office suite and, in particular, MS Excel.
Today’s review is devoted to the add-ins designed for the automation of search and replace operations in Excel.

Name of the add-in Version Vendor Price Excel supported
Advanced Find & Replace for MS Excel 3.1.2.42 Ablebits $29.95 Excel 2000-2007.
Find & Replace for Excel 1.1 MAPIlab $39.00 Excel 2000-2007
Advanced Find & Replace   PATools $25 Excel 97-2007

Advanced Find & Replace for Excel from Ablebits

Supports: Excel 2007, Excel 2003, Excel XP, Excel 2000.

The Find & Replace add-in from Ablebits supports all Microsoft Excel versions from 2007 to 2000. The installation process went quickly and smoothly as ever, in the best traditions of AbleBits’ add-ins. After starting Excel the Find and Replace icon immediately took its habitual place on the Excel toolbar:

A click on the icon opens the Find & Replace panel (by the way, the second click hides it). The add-ins panel opens in the Search in tab first:

On this tab you can:

  1. Search through multiple Microsoft Excel worksheets and workbooks, using the Find what field.
  2. Select workbooks and sheets for search. By default the plug-in searches in ALL opened workbooks.
  3. Search in different types of data: cell values, formulas, hyperlinks and comments separately or simultaneously. (The Look in option).
  4. Select the direction of search. (Columns or rows).
  5. Use match options. (Match case and entire cell).


The screenshot above shows you the Search Results tab.

Pay your attention to four really nice features of this Find and Replace plug-in:

  1. The Find all button has two alternative options. If you click on a little arrow to the right of it, you can search in the selected region or in search results.
  2. In the Search results tab, you see at a glance the content of cells, their location and the type of found data. So, you have the whole picture of search in front of your eyes.
  3. At the bottom of the Search results tab you see the total number of found entries.
  4. If you click on any found entry, you will be immediately taken to this cell in the worksheet.
  5. You can export search results to a new workbook by just selecting appropriate found entries and right clicking on them.

You can set the search range directly in the worksheet or in the add-in’s pane.

Now, just select the cells in which you want to replace data.

Clicking on the Help button will open the built-in Help system supplied with excellent screenshots and step-by-step instructions on how to do search and replace across your multiple Excel worksheets.

The About button will give you complete information about your license.

For me such realization of the user interface is exactly how the add-in should look like for pleasant and comfortable work. At any moment, at any stage you can cut in the adds-in’s work and make necessary changes. I have seen enough plug-ins in which the sequence of user actions was predetermined and the best thing you can rely on was the Undo button.

In case something goes wrong you have to roll back and change the settings. The truth is that in case of some monotonous work which fully applies to the standard Find / Replace function, this is in no way the best alternative.

That’s a horse of a different color when you have some freedom of actions and can concentrate on your work. The AbleBits’ approach to developing add-ins pleased us more than once and their Advanced Find & Replace for Microsoft Excel once again has proved the earlier made conclusions about nice Excel add-ins of this company.

Find & Replace for Excel from MAPIlab

Supports: Excel 2007, Excel 2003, Excel 2002, Excel 2000.

The installation process will not raise any questions or difficulties. At one of the steps (which has become a tradition for MAPIlab plug-ins), you will see a dialog window in which you can choose the installation for one or all users.

After the completion of the installation process you will see the add-in’s wizard which is again quite typical for MAPILab add-ins. At the first stage the user will be asked to select the language.

Then you will be requested to enter a serial number or, if you don’t have any, you can keep on using the add-in in a trial mode.

After running Excel, the Find and Replace toolbar will show up in the Excel toolbar area:

The toolbar has two sections: “Help”, as it is supposed to, will bring you into the built-in help system:

Here I was faced with one confusing thing – by default all screenshots are hidden and you see only “Show screenshot” links. Is it good or bad, one will decide for himself, as to my style of work, I am more used to some quick looking through pictures rather than careful reading of text. Besides, the interface of such add-ins is not supposed to pose any puzzles for the user.

The Settings option that allows some customization of the add-in menu, to my mind, deserves your attention as well.

Well, let’s proceed to work… Clicking on the MAPIlab Find & Replace button will bring up the same wizard:

At the first step, you choose between the two base functions Search and Replace or just Search. Preview and Backup options look quite natural if to take into account that all work with the add-in is performed through the wizard.

At the next step you select sources for search: workbooks, sheets or cell ranges. I already had a test table opened, so I just selected it from the list suggested me by the add-in. This action led to the following result:

After clicking OK, I just saw a dialog window again. My table was not added. The same thing occurred repeatedly at every further attempt whether I selected the region, a worksheet, a workbook or a folder. I tried this add-in on my work PC that has Windows 2000 and Office 2003 with all updates and service packs installed. Unfortunately, I cannot afford changing the configuration of my machine to make further attempts to get this add-in to work. Well, after some thought, I decided to continue making acquainted with the add-in by screenshots on the vendor’s web-site.
The next window is the rules manager:

It must be admitted that, in my view, for such a trivial task as search and replace such approach seems to be a bit immoderate and excessive : Well, you create the rule:

Here we see the options similar to those of the AbleBits Find and Replace plug-in. But, when working with the AbleBits add-in you can also see your Excel sheet, with the MAPIlab add-in you are bound to the wizard window. Also, a step-by-step principle of work imposes some limitations on selecting a search region. If it’s changed, you need to go though all the steps from scratch. Finally, the way of presenting results also limits your freedom in some way.

Yes, you see the result of your actions. But the whole picture is beyond your reach: how does it look like in the context of the entire sheet? It is the step-by-step scheme of work that led to the emergence of Back up and Preview options which to my mind are excessive and complicate the whole matter.

Advanced Find & Replace from PATools

Supports Excel 97-2007

The installation of this add-in was successful but by no means standard. The PATAFInstaller.xls file was downloaded from the vendor’s web-site, and then it was opened in Excel with the following result:

The installation took place in Excel itself. The add-in suggested the location for the installation folder:

Then the PATools Advanced Find & Replace asked to establish an internet connection in order to download setup files:

After the download and installation processes are completed you will see the following message:

The startup button of the add-in took up its position in a rather unusual place:

It proved impossible to see the add-in’s menu without opening an Excel workbook:

The main window of the add-in has the following look:

At first sight the add-in’s interface doesn’t look very intuitive: What the user is expected to do then? Right, he looks up for instructions in Help. But where is it? Finally I managed to find it, rather accidentally I must admit. For this you are to click on the arrow right to the Select option and select Help from the dropdown list:

Help is available on the vendor’s web-site only and unfortunately without any screenshots. Well then, let’s try the add-in in action…

Ugly fonts and quite doubtful abilities compared to the standard Excel Find and Replace. Moreover, when just another record is found the Replacement field never becomes active. My attempt to find all, in this add-in interpretation Find al gave me just an opportunity look through all records (with no chance to change anything), and subsequent displaying of the main window again.

No step-by-step, or howto manual was found on the vendor’s site. At this stage I decided to break off with this Find and Replace add-in.

Thus, the only functioning search and replace plug-in from the three reviewed today proves to be Advanced Find & Replace for Microsoft Excel from AbleBits. Their approach to managing search results is exemplary. This is exactly what the standard Excel function lacks and what was missed by MAPIlab either. Visual displaying of search results in such a way that you can see the whole picture at a glance. It’s disappointing that such things are regarded as something special. Well, that makes us even more pleased when we find the product meeting all our requirements.

Advanced Find & Replace by Ablebits Find & Replace for Excel by MAPILab Advanced Find & Replace by PATools
Installation 5 of 5 5 of 5 3 of 5
Usability 5 of 5 4 of 5 2 of 5
Functionality 5 of 5 4 of 5 3 of 5
Help 5 of 5 5 of 5 3 of 5
Total 5 4.5 3

Comments are closed.