Advanced Find & Replace for Microsoft Excel

The Advanced Find and Replace add-in for Excel is designed to search across multiple worksheets and replace the found entries, select, or export them.

Start Find & Replace

To start your search, click the Find and Replace icon in the Search group on the Ablebits Tools tab:

Run Advanced Find and Replace.

Tip.You can also press the Ctrl+Alt+F hotkey on your keyboard to run the tool. To change the default hotkey, use the Options icon at the bottom of the add-in window.

Search in Excel workbooks

On the Advanced Find and Replace pane, configure your search options:

How to search in Excel workbooks.

  1. Enter the value you are going to search for.
  2. Pick the Search within option:
    • Select Entire Sheet if you need to scan the whole document to find the value.
    • Tick Selection Only if your area of interest is just a certain range rather than the entire worksheet.
      Tip. You can specify the search option right away by clicking the down arrow below the add-in's icon and picking the necessary option from the drop-down menu:
      Choose to search within all workbooks or a selection.
  3. Decide if the add-in should go through Values, Formulas, Comments, Hyperlinks, and/or Errors and check the needed option(s). You can tick all these boxes to perform search in all types of records at the same time.
  4. The Match options section adds certain flexibility to defining the values to look for:
    • If you want to distinguish between uppercase and lowercase characters, select Match case. For example, to find the name "Robert" and exclude entries like "robert.t@domain.com", enter "Robert" into the field at the top, check the Match case box, and you'll see all entries that begin with an uppercase letter.
    • Check the Entire cell box 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.
  5. Tick the boxes near the workbooks and worksheets you want to scan.
  6. Click the Options icon to select the shortcut you want to use to start the add-in:

The shortcut for Advanced Find and Replace.

When you are finished with the settings, click the Find All button.

Replace, select, or export the found entries

Now you can work with the search results:
 Search in Excel and replace, select, or export the found entries.

  1. If you want to replace the found entries, enter the new value into the Replace field.
  2. Click the Replace all button to change all the found values in one go.
  3. If you need to replace only certain found entries, select a single search result or pick multiple cells by holding the Ctrl key and use the Replace button to update only the highlighted records.
  4. To immediately see where exactly the found data are located, highlight the entry of interest in the search results field to navigate to its original location and get it selected in your worksheet.

    You can sort the search results by the Value column and change the width of each column right in the add-in window.

  5. Export the search results to a new workbook by clicking the Export all button.

    Take advantage of the additional options and choose the data to export. Click the arrow next to Export all and select one of the options from the drop-down list:
    Choose the entries to export after the search in Excel.

    • You can export only rows or only columns with the found values by choosing to Export rows with all found entries or Export columns with all found entries.
    • Export only certain entries by highlighting them and selecting to Export the highlighted entries in the drop-down list.
    • Export rows or columns with the highlighted entries by picking one of the last two options. Press the Ctrl key, select the entries in the add-in pane, and choose to export rows or columns that contain them.
  6. By clicking the Select cells button, you can get all the found items highlighted in your worksheets.

    Besides, if you click the arrow next to Select cells, you'll be able to choose an option to highlight the rows or columns with the found entries:

    Select the found entries in Excel.

    • Select only rows or only columns by choosing to Select rows with all found entries or Select columns with all found entries.
    • You can get only certain entries selected by picking them in the tree view and choosing to Select the highlighted entries.
    • Select rows or columns with the entries of interest by picking one of the last two options. Press the Ctrl key, highlight the entries in the add-in pane, and choose to select rows or columns that contain them.

Responses

Norman G Fisher says:
December 14, 2019 at 11:17 pm

I like the "Search and Export" feature but Abelbits exports to a new worksheet every time. I want to put all the results on one worksheet. Can this be done?

Katerina Bespalaya (Ablebits Team) says:
December 16, 2019 at 12:33 pm

Hello Norman,

Thank you for your feedback. Unfortunately, it is only possible to export the found entries into a new workbook in the current version of the add-in. However, I have forwarded your request to our developers, they will look into the possibility to implement this feature. Thank you for taking time to write us.

I want the name of the sheet to be easy to see at a glance when I search.
In the column of Address, there is only one sheet name, and all the rest are only the address of the cell.
When scrolling down, the sheet title is not shown but only the address of the cell is shown. I am uncomfortable with this.

Hi Mohamad,
Thank you so much for your detailed feedback.
1. Now you can’t clear the search list, but it is a great idea to make it possible, so we’ll consider implementing the feature into our future releases.
2. Concerning the Korean search, the add-in should support it. To find the causes of the problem you face, we need to see the type of data you are working with, so if possible, please send us a small sample of your data to support@ablebits.com. Please remove any sensitive information.
3. Thank you for pointing out this inconvenience. This is one more idea for us to become better, thank you, it is taken into consideration.
Please do not hesitate to contact us with any other questions or comments.

1. I do not want the search list to remain. How do I clear a search list?
2. And it seems that ablebits does not fully support Korean search. For example, if I had searched for the word "텍스트" in the past,
The next time I type the word "텍스트", the letter is broken. just like "텏ㅡ".

Post a comment

Seen by everyone, do not publish license keys and sensitive personal info!

If you have any questions or issues with this add-in, please feel free to post your concerns in the comments area. As soon as we answer, a notification message will be sent to your e-mail. If you do not want to share your thoughts in public, please contact us at support@ablebits.com.