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:


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


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 😉


  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 *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Latest Posts