Advanced Find and Replace in Excel

This tool extends the capabilities of Excel's Find and Replace with a handful of useful features:

• Find and replace in all open workbooks or only selected worksheets from different workbooks.
• Search simultaneously in values, formulas, hyperlinks and comments.
• Export found entries to a new workbook in a mouse-click.
• And a lot more options.

Open your worksheet and click the Find and Replace button on the Ablebits Tools tab. To search, replace, export, and select, do the following:

1. Enter a character, word or string to search for.
2. Select workbooks and sheets you want to look in. To search only in the active sheet or selection, check the corresponding option.
3. Choose data type for your search: values, formulas, comments, hyperlinks, or all at once.
4. To look for case-sensitive data, select the Match case option.
5. To find the exact matches, tick off the Entire cell checkbox.
6. Click Find all.

You will see a list of all found entries. Now you can replace all or some of them with another value, export the entries to a new workbook, or select and highlight cells, rows, or columns.

More information can be found here.

Yes, you can search for any characters in comments, just select a corresponding option. Moreover, you can choose to look in formulas and even hyperlinks. And values, of course.

Sure! The simplest way is to open all workbooks to look in, and then start Advanced Find and Replace. By default, all worksheets in all open books will be selected.

Yes. When you start the add-in, all sheets from all open workbooks are selected, but you can unselect the ones you don't need.

Yes. To do a case-sensitive search, just tick off Match case.

Sure. It is up to you whether to replace all found entries, or only some of them. You can choose the entries to replace on the add-in pane.

Yes. To find only exact matches, select the Entire cell option.

When search is finished, the found entries are displayed on the add-in pane, and you can export them to a new workbook. There are several advanced options for you to choose from:

• Export rows or columns with found values.
• Export highlighted entries, i.e. the items you selected within search results.
• Export rows or columns containing highlighted entries.

Yes, just click the Replace all button.

Of course. The Select cells button offers you five options:

• Select rows with all found entries.
• Select columns with all found entries.
• Select the highlighted entries.
• Select rows with the highlighted entries.
• Select columns with the highlighted entries.

Yes. For this, use the Search for Typos option. Choose the range, run the utility, enter the max number of different characters and the max number of characters in a word or cell. Click Search for typos button. You will see a list of all values that meet your search criteria. Then you can choose the correct values and replace the incorrect ones.