CODEWITHSUNDEEP
excel
cotso
cotso

Reputation: 33

find string within excel formula text

is there a way to catch all cells containing #REF! in their functions (not in their result), preferably without using vba?

example: i would like to catch cells containing e.g. this:

=+SUBSTITUTE("#REF!","#REF!","text")

1) since the displayed result of the cell is "text", which is not an error, =+ISERROR() won't work.

2) i have also tried =+SUMPRODUCT(ISNUMBER(SEARCH("*ref*",A:A))*1) but that doesn't work either.

3) the only thing i was able to achieve, was to identify the first cell in a column containing "#" with the use of =+MATCH("#",A:A,-1) but that's only half of the answer since i can't/ do know how (?) to use it over several columns.

4) last but not least, i'm on excel 2010, so no =ISFORMULA() available.

any ideas/ help will be greatly appreciated! thanks!

Upvotes: 2

Views: 165

Answers (1)

pawelty
pawelty

Reputation: 1000

How about find & replace and substituting "=GetData" with "^^GetData" (replacing the = with some character(s) which don't exist in your formulae)? Then you can use SEARCH on it and once you're done you replace back "^^" with "=".

Upvotes: 2

Related Questions