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

"Toppers" wrote:

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

"Rianne" wrote:

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.

"Toppers" wrote:

Try this, entered with Ctrl+Shift+Enter:

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

HTH

"Teethless mama" wrote:

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


"Rianne" wrote:

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.