Find and Replace Dreams

Is it just me, or if the Find and Replace tool in Excel (actually in Office) really limited?

The first deficiency is that I want to be able to select "Within Selection", not within "Sheet". I know that it defaults to the selection if you try this on a multi-cell selection, but if you want to replace references in a single formula in a single cell… watch out! I usually copy my formula to notepad and do the work there.

Now, the options above are fine for really simple find/replace scenarios, and have served us well for years, I suppose. Yet it still leaves me wanting, especially right now. Consider this formula:

=ROUND($D$69*(SUM($AG66,$E66:AQ66)+SUM($AG93,$E93:AQ93)+SUM($AG120,$E120:AQ120)+SUM($AG157,$E157:AQ157)-SUM($AG163,$E163:AQ163))/SUM($AV$66,$AV$93,$AV$120,$AV$157,-$AV$163)-SUM($AG69,$E69:AP69),0)

It's pretty hideous, but I need to modify it to read as follows:

=ROUND($D$69*(SUM($AP66:AQ66)+SUM($AP93:AQ93)+SUM($AP120:AQ120)+SUM($AP157:AQ157)-SUM($AP163:AQ163))/SUM($AV$66,$AV$93,$AV$120,$AV$157,-$AV$163)-SUM($AP69:AP69),0)

So pretty much, what I need to do is

• Replace all the $AG references with $AP
• Get rid of the ,$E##? pieces

The existing tools works fine for the first part, but can't do the second since the ## pieces change.

Wouldn't it be nice if Excel had pattern matching? I'd love to be able to knock up a search to check for a pattern like comma dollar E number number [number] colon and replace it with a colon.

Even though this might seem a little strange to have a variable pattern here, I'm pretty sure that you can do exactly this with Regular Expressions, and quite quickly too.

What do you think? Something you could see value in?