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.

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

--
Don Guillett
SalesAid Software

"Rianne" wrote in message
...
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.

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


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

Many instances, but will only return the caution once.

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

"Toppers" wrote:

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


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

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

"Rianne" wrote:

Many instances, but will only return the caution once.

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

"Toppers" wrote:

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


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

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

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.

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

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.

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.

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

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.