Wednesday 20 May 2015

AET Find and Replace v1.2

A while back, Jeff Weir, author of the upcoming Excel for Superheroes & Evil Geniuses and supreme guest blogger at the Daily Dose of Excel asked me about adding some functionality to my AET Find and Replace tool. (Jeff devotes part of his book to Find and Replace that I think you'll find interesting reading, a blurb on my tool is included!)

And here it is - the new improved version with lots of goodies!

Find and Select

Find and Replace
 photo AET Find And Replace v1.2 2 s_zpszxklvcgh.jpg

Let's take a closer look...

Adding Fields
As the name suggests, the simplest way is to click the Add Fields button, which adds a row of fields to the bottom of the form.

 photo AET Find And Replace v1.2 3 s_zpsuuxgfyya.jpg

You can also use the Enter Fields From Range button (copy values for Find, Replace and Exclusion fields directly from cells) or the little blue + buttons to insert a row of fields directly above the row that the button is next to.

Deleting Fields
Again, use the Delete Fields button to remove the row of fields at the bottom of the form.

You can also use Clear All Fields to remove all fields except the top one. It's fields will show as blank.

Or you can use the red X buttons to remove the row of fields that the button is next to.

Part or Whole Option Buttons
Use the Part and Whole option buttons at the top to set the default for all field rows. You can also set individual rows to Part or Whole if required.

Note that you can use * and ? as wildcards in searches.

* can be used for multiple characters such as E*l to find Excel (or Evil in Jeff's case)

? can be used for single characters such as W?rd to find Word (but who would want to?)

I can see you looking at that. Panting as you do so.

Okay, one of Jeff's coolest ideas was the ability to use Exclusions to be ignored in searches. (Along with entering values from ranges, deleting field rows directly and part/whole settings per field row).

He also said it would be good if users could use multiple exclusions per field row using commas.

Here's an example. Note there are 2 exclusions for the first row of fields.
 photo AET Find And Replace v1.2 4 s_zpsb5rg9u9o.jpg

And the end result.
 photo AET Find And Replace v1.2 5 s_zpsyet8u35j.jpg

Here's the download link. (Excel 2007 and above)


  1. I love this tool, Andrew. Like I said in my book, I can't believe MS don't call out on their own anemic Find and Replace tool that it uses wildcards. How are users supposed to know this stuff? So I'm glad your tool calls it out. Fantastic stuff!

  2. I have your suggestions to thank for it Jeff. I agree wildcards, and a few other things, could be better advertised. I guess that's why the majority of users only have a basic grasp of what Excel has to offer.

    Anyway, I'm looking forward to reading your book when it comes out!