"grep" the formulas?

  • Thread starter Thread starter Art Decco
  • Start date Start date
A

Art Decco

Is there some way to ask Excel to identify all the cells that contain some
particular content in the formulas themselves? For example, I'm finding that
the MROUND function is causing some trouble, so I'd like to find every place
in a very large workbook where "MROUND" occurs in a formula and rewrite that
formula. I've run into this before, where I've wanted to find every formula
that contained some particular character sequence, and some of the formulas
are very long and very numerous. What I'd like to be able to do is "grep"
the formulas and highlight the matches.

Does Excel offer anything of that sort?
 
I'm not sure what "grep" is, but the Find and Replace
actions from the Edit menu will find all occurrences of
the word "MROUND" in formulae in the worksheet.

If you want to highlight the cells that contain "MROUND",
go to the Edit menu, select Replace. For "Find What?",
enter "MROUND". For "Replace With", also
enter "MROUND". Click the Format button, then select
Patterns and pick the color with which you would like to
format. Click OK and then select "Replace All".

The formula will stay the same, but all cells with this
formula will be highlighted.

Mike.
 
Thanks!

Curious that the term "grep" would cause such a commotion. I've been a
professional Win32 SDK & MFC programmer for years, and guys like me use it
all the time. It certainly came from the Unix world originally, but then so
did sockets, ports, FTP, telnet, the Web, and countless other things that
are part of Windows today. It's a *specific type* of "find", referring to
scanning for text patterns, often via "regular expressions", but perhaps
it's too obscure for a non-programmer forum.

For what it's worth, I've had some form of grep available on my Windows
command line for at least ten years. It comes standard with MacOS X, on
which I also work (and which is also a common Excel platform), and it is a
built-in keyword in the Windows and Mac versions of Perl, which many of us
Win & Mac programmers use for manipulating C/C++ source files.

Oh, well. Yes, I meant a type of "find", and Mike's answer is exactly what I
was looking for.

Thanks again.
 
Harlan Grove said:
...

global-regular expression-print

an old ed mnemonic.

So does this mean I can start using Windoze?
Click to expand...

Why, yes, I believe it does. And don't forget your cygwin. I don't leave
/home without it. :-)
 
Back
Top