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 in Excel.

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

Search in Excel workbooks

You will see the add-in window:

How to search in Excel workbooks.

  1. Enter the value you are going to search for into this field.
  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:
      SChoose to search within all workbooks or a selection.

  3. Decide if the add-in should go through Values, Formulas, Comments, and/or Hyperlinks 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 Find and Replace in Excel.

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.
    • Feel free to 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

Is there a way where I can search for different values at once across sheets or workbooks?

Reply

Hello, Abdul,
Thank you for your comment.
Unfortunately, there is no way to search for different values at once, the tool can look for only one value at a time.
Thank you.

Reply

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 "텏ㅡ".

Reply

The above comment is about "Advanced Find and Replace".

Reply

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.

Reply

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.

Reply

Ask a question (posted publicly)

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.
Privacy policy Terms of use Contact us

Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.

Ultimate Suite 2018.5 for Excel
60+ professional tools for Excel 2019-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