# 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?

This entry was posted in Excel, General by Ken Puls. Bookmark the permalink.

I'm the blog owner... My main website is located at www.excelguru.ca, and is devoted to holding tips and tricks on excel development.

## 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

3. Hi Ken,

Can you not do,

Replace ,\$E*: with :

4. Andy, I was really hoping for something more complicated... 🙂