Advanced Find & Replace for Microsoft Excel

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

Search & replace in multiple Excel workbooks
Free Download
7-day fully functional version


Find information in all open workbooks

To start the search, click the Find and Replace icon in the Search group on Ablebits Tools tab, or press the Ctrl + Alt + F hotkey on your keyboard. You will be able to change the default hotkey using the Settings icon at the bottom of the add-in window.

Find Replace Tool

You can pre-define the range for the search right in the toolbar: click on the name of the tool below the add-in icon to open the list and choose if you want to scan all open Excel files, just the selected range, or your active worksheet:

Pre-select the range for the search in the toolbar

Enter the value you’d like to find in the field at the top and adjust settings on the Search in tab to get just the results you need:

Search options - Find & Replace for Excel
  1. Decide if the add-in should go through Values, Formulas, Comments, and/or Hyperlinks and check the corresponding option(s) in the Look in section. You can tick all these boxes to perform search in all types of records at the same time.
  2. Unless you pre-defined the data to check at start, you can set it to all open workbooks, the current sheet, or the selected range in the Search within section. To scan specific workbooks and sheets, select them in the workbooks tree below the Options area.
  3. Match options section gives you more flexibility for defining the searched values:
    • If you want to distinguish between uppercase and lowercase characters, select the Match case checkbox. For example, if you want to find the name "Robert" and exclude entries like "robert.t@domain.com", enter Robert into the field at the top, select the Match case check box and you'll see all entries with an uppercase first character.
    • Check the Entire cell checkbox to search for an exact and complete match of the characters specified in the top field. I.e. if you select this option with the value "Robert", you’ll find only those cells that don’t contain any other values.
    Tip. Selecting this option will also let you use asterisk * and question mark ? as wildcard characters to find entries that have other details within a cell. Use the question mark ? to substitute one character and the asterisk character * to substitute a string. E.g. you can see all cells that contain the word "Part" at the end by entering "*Part" into the field at the top. If you want to find entries with any character after the word "Part", like Part A, Part B, etc., look for "Part ?".

The Options section will list your choices at the top. Feel free to hide search settings by clicking the collapse arrow icon next to the summary, or re-open them using the expand arrow icon.

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

The number of found items will be marked next to the Search results tab and you will see them all listed and organized by file, sheet, and cell address.

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 Value column and change the width of each column right in the add-in window.

How to replace the found values

  1. Perform the search first (see How to find information).
  2. Enter a new value in the Replace area field.
  3. Click the Replace all button to change all the found values at once. You can select a particular search result, or pick multiple found entries by holding the Ctrl key, and use the Replace button to update just the highlighted records.
Note. The entire cell with the result is replaced if you used wildcard characters for the search.
Found Values - Find & Replace for Excel

How to export search results

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

Expanding the export to rows or columns with the found values proves to be especially useful when you need to extract some data from your Excel table. To do this, click the arrow next to the Export all button and select the option that suits you from the drop-down list.

Found Values - Find & Replace for Excel

As you can see, three options at the bottom of the list let you get a certain set of records in a new sheet: press the Ctrl key, highlight the entries in the results, and choose if you want to export the values or the entire rows/columns that contain them.

How to select the cells with the found values

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

Select the cells with the found values

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 with the help of the Ctrl key. 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 your message on our support forum
 

Our working hours: 0am to 10am PST; 3am to 1pm EST; 8am to 6pm CET.

Ultimate Suite for Excel Professionals
 
 
60+ professional tools for Excel 2016-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
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
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
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
 
 
Excel add-ins and Outlook tools - Ablebits.com