Advanced Find & Replace for Microsoft Excel

Ssearch & replace in Excel values, formulas, text, comments and hyperlinks

Search & replace in multiple Excel workbooks
Free Download
Free 15-day trial version, 9/12/2014


Find information in all opened workbooks


Open Advanced Find & Replace by clicking on its icon ico or pressing the Ctrl+F (Ctrl+Alt+F) hotkey.

Tip

Select the hot key combination for opening the add-in

By default Advanced Find and Replace is opened with the standard Ctrl+F hotkey. If you'd like to keep it for the standard Excel Find and Replace dialog box, you can use the Ctrl+Alt+F combination to start the add-in instead. When you open the Advanced Find and Replace pane, you can specify the hotkey combination you want to use, if any. You can find the Shortcut section in the bottom right corner of the pane.

Search options - Find & Replace for Excel
  1. Enter the information you want to search for in the Find what field on the Search in tab.
  2. Decide if the search should be performed in Comments, Formulas, Values or Hyperlinks and check the corresponding option(s) in the Look in section. You can tick all of these options to search in values, formulas, comments and hyperlinks at the same time. Select the type of entries you want to find
  3. If you want to distinguish between uppercase and lowercase characters, select the Match case checkbox in the Match options section.
    So if you want to find the name "Robert" and exclude entries like "robert.t@domain.com", enter Robert into the Find what field, select the Match case check box and you'll see all entries with an uppercase first character. Select Match case for case-sensitive search
  4. Check the Entire cell checkbox to search for an exact and complete match of the characters specified in the Find what box.
    Find a complete match of the entry with a cell by selecting this check box

    Tip

    You can use wildcard characters to find an entry within a cell that has other information. Use the question mark "?" to substitute one character or the asterisk character "*" to substitute a string. E.g. you can find all cells that contain the word "Part" at the end by entering "*Part" into the Find what field. If you want to find entries with any character after the word "Part", like Part A, Part B, etc., search for "Part ?".

    To use the asterisk "*" or the question mark "?" as wildcard characters for the search, the Entire cell checkbox must be selected.

  5. In the Workbooks tree choose the workbooks and worksheets you want to search in. By default the search is performed in the current worksheet.

    Note

    If you have more than 2 cells selected, then the search will be performed only in the selected range. To select all opened workbooks and worksheets click the Check all button at the top of the Search in tab.

  6. Click the Find all button. The add-in will perform the search according to the parameters you set. It may take a few minutes if you search in several large workbooks.

Work with the found items

Found Values - Find & Replace for Excel

You will see the found items on the Search results tab.

Navigation in search results

  • When you highlight an entry in the search results, the add-in navigates to its original location and selects it in your worksheet. You will immediately see where exactly the found data are located.
  • You can sort search results by the Type or Value columns.

How to replace the found values

  1. Perform the search first (see How to find information).
  2. Enter the replacement characters in the Replace with field.
  3. Click the Replace All button to replace all the found values. If you want to replace only some particular occurrences, you can select these search results by holding the Ctrl key. Then click the Replace button.

Note

If you used wildcard characters for the search, the entire cell with the result is replaced.

How to export search results

Found Values - Find & Replace for Excel

You can export search results to a new workbook for further work. To do this click on the Export all button at the bottom of the Search results tab.

Besides exporting all found entries, you can expand the export to rows or columns with the found values. You can also highlight some entries in the results and export them, or export the entire rows or columns that contain them. To do this click on the down arrow next to the Export all button and select the option that suits you from the drop-down list.

How to select the cells with the found values

Found Values - Find & Replace for Excel

By clicking the Select cells button at the bottom of the Search results tab you can select all the found items in your workbooks.

You can choose the option to select the rows or columns with the found entries instead. Or you can narrow the view down to the entries you highlight in the search results. To do this just click on the down arrow next to the Select cells button and select the necessary option from the drop-down list.

Featured customers

Ablebits.com featured customers
 
Contact us
 
  Publish this message on the forum
 

Please remember that we work from 8am to 4pm GMT,
this is 4:00am to 12:00pm EST, 1:00am to 9:00am PST, and our public holidays may differ from yours.

Ultimate Suite for Excel Professionals
 
 
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard