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?

4 thoughts on “Find and Replace Dreams

  1. Hey Ken,

    Just edit the formula, take out the leading =, thus making it a text cell, search and replace, pop the = back in, formula it is...

    Just my 2 cents 😉

    Steve

  2. Hi Ken,

    Dude, that formula is insane! I would create a UDF for that gnarly beast, myself. Or at least use Range Names! Mama mia! 😀

    I agree that regular expressions would be a very natural next step for find/replace. It's probably a bit advanced for the basic user, but it's a concept that's certainly extremely popular among programmers and is growing in familiarity with non-programmers.

    I would also include a simple wild-card version though (something that operates along the lines of the VBA 'Like' keyword) as a middle ground. Full-featured regular expressions would be beyond the abilities of the average user.

    My guess is that this will have to happen eventually.

    - Mike

Leave a Reply

Your email address will not be published. Required fields are marked *