Too Many If's

[Guest]

I'm trying to write a formula that will locate an asterisk (*) in a large
range of data, and if it is found, put a statement like "Hey, I found an
asterisk!" in one cell. I can't seem to get my head around the formula, asn
I've only been able to replicate what I want by using multiple IF statements.
I have over 200 cells that could have the asterisk in it. Please help, it
would be greatly appreciated.

 

[Don Guillett]

Are you looking for one (or many) instance(s) of t *? Where in the data?
Examples.

 

[Guest]

Try this if just want a message to say you have found (at least) one (*) in
the range:

=IF(SUMPRODUCT(--(ISNUMBER(FIND("*",A1:B10)))),"hey, found","")

HTH

 

[Guest]

Many instances, but will only return the caution once.

A B C D
1 * *
2 * *
3 * * * *
4 *
5 * *

 

[Guest]

However, there may be a double asterisk....(**) and I don't want the caution
to appear....too difficult?

 

[Guest]

=IF(LEN(A1)-LEN(SUBSTITUTE(A1,"*",""))=1,"Found asterisk","")

 

[Guest]

I'm sorry, but I can't seem to get this formula to work for the range that I
have. It only seems to work for a single cell, and not a range of cells.

 

[Guest]

Try this, entered with Ctrl+Shift+Enter:

=IF(SUMPRODUCT(IF(LEN(A1:B5)-LEN(SUBSTITUTE(A1:B5,"*",""))=1,1,0)),"Found","")

HTH

 

[Guest]

Thanks Toppers, it worked great. I'm curious as to why it won't work if I
substitute a double asterisk in. It's not a huge deal, but just a curiousity.

 

[Guest]

.... because the formula below substitutes all asterisks by Null (i.e. removes
asterisk from the string).

If there is more than one asterisk, the formula will give an answer > 1 so
the IF(LEN(A1:B5)-LEN(SUBSTITUTE(A1:B5,"*",""))=1,1,0) will give an answer
of 0, but for 1 asterisk we will get 1.

SUMPRODUCT processes all cells in the range in this way so we get a value of
0 or 1 for each cell.

SUMPRODUCT adds all the 1s and 0s and if we get answer other than 0 ... so
there must be at least one cell with a single asterisk ....(effectively a
TRUE condition) we get the "found" message.

HTH

 

[Guest]

I should have added that the Ctrl+Shift+Enter makes it an "array formula"
which processes the whole array (or range).