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?