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

If you have a comment or question about the blog post content, please feel free to post it here. If you need help adapting this solution to your own needs, please post in our free help forum.

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