Find a "9" within a range using a function (T/F)
 

I need to try to cobble together a function nest or whatever I have to do, which will tell me if the number 9 exists anywhere within
a range of cells. The 9 could be just one character of a larger number (i.e. 1496), or it could be just a nine. I know that I could
use filtering or use the find feature with the dialog box, but I need a formula instead. Also, I just need to know a true or false;
I don't need to know where the nine is or any other info. I just need to know if it is there. I'm stumped. Any ideas?

Thanks in advance!
--
RMC,CPA

Find a "9" within a range using a function (T/F)
 

Hi!

Try this:

=SUMPRODUCT(--(ISNUMBER(FIND("9",A1:B12))))0

Biff

"R. Choate" wrote in message
...
I need to try to cobble together a function nest or whatever I have to do,
which will tell me if the number 9 exists anywhere within
a range of cells. The 9 could be just one character of a larger number
(i.e. 1496), or it could be just a nine. I know that I could
use filtering or use the find feature with the dialog box, but I need a
formula instead. Also, I just need to know a true or false;
I don't need to know where the nine is or any other info. I just need to
know if it is there. I'm stumped. Any ideas?

Thanks in advance!
--
RMC,CPA

Find a "9" within a range using a function (T/F)
 

Assuming your range of cells is A1:B5...

=IF(SUMPRODUCT(LEN(A1:B5)-LEN(SUBSTITUTE(A1:B5,"9","")))<0,FALSE,TRUE)

HTH,
--
Gary Brown

If this post was helpful, please click the ''''Yes'''' button next to
''''Was this Post Helpfull to you?".


"R. Choate" wrote:

I need to try to cobble together a function nest or whatever I have to do, which will tell me if the number 9 exists anywhere within
a range of cells. The 9 could be just one character of a larger number (i.e. 1496), or it could be just a nine. I know that I could
use filtering or use the find feature with the dialog box, but I need a formula instead. Also, I just need to know a true or false;
I don't need to know where the nine is or any other info. I just need to know if it is there. I'm stumped. Any ideas?

Thanks in advance!
--
RMC,CPA

Find a "9" within a range using a function (T/F)
 

Hi,

First - thank you. Your solution DOES work. My only comment is that it throws an error if no 9. You probably just assumed I could
take it from there; you're right, I can, and I appreciate it. Yours is a nice, short solution that does work.

Richard

--
RMC,CPA


"Biff" wrote in message ...
Hi!

Try this:

=SUMPRODUCT(--(ISNUMBER(FIND("9",A1:B12))))0

Biff

"R. Choate" wrote in message
...
I need to try to cobble together a function nest or whatever I have to do,
which will tell me if the number 9 exists anywhere within
a range of cells. The 9 could be just one character of a larger number
(i.e. 1496), or it could be just a nine. I know that I could
use filtering or use the find feature with the dialog box, but I need a
formula instead. Also, I just need to know a true or false;
I don't need to know where the nine is or any other info. I just need to
know if it is there. I'm stumped. Any ideas?

Thanks in advance!
--
RMC,CPA

Find a "9" within a range using a function (T/F)
 

Hi Gary,

Thanks for your solution. I did have to reverse the true and the false in your formula because I was getting a false when the 9 is
present, but after I made that change, I liked it and it worked great.

Thanks again,

Richard
--
RMC,CPA


"Gary L Brown" wrote in message ...
Assuming your range of cells is A1:B5...

=IF(SUMPRODUCT(LEN(A1:B5)-LEN(SUBSTITUTE(A1:B5,"9","")))<0,FALSE,TRUE)

HTH,
--
Gary Brown

If this post was helpful, please click the ''''Yes'''' button next to
''''Was this Post Helpfull to you?".


"R. Choate" wrote:

I need to try to cobble together a function nest or whatever I have to do, which will tell me if the number 9 exists anywhere
within
a range of cells. The 9 could be just one character of a larger number (i.e. 1496), or it could be just a nine. I know that I
could
use filtering or use the find feature with the dialog box, but I need a formula instead. Also, I just need to know a true or
false;
I don't need to know where the nine is or any other info. I just need to know if it is there. I'm stumped. Any ideas?

Thanks in advance!
--
RMC,CPA

Find a "9" within a range using a function (T/F)
 

It does not throw an error, it returns TRUE of FALSE

--

Regards,

Peo Sjoblom

"R. Choate" wrote in message
...
Hi,

First - thank you. Your solution DOES work. My only comment is that it
throws an error if no 9. You probably just assumed I could
take it from there; you're right, I can, and I appreciate it. Yours is a
nice, short solution that does work.

Richard

--
RMC,CPA


"Biff" wrote in message
...
Hi!

Try this:

=SUMPRODUCT(--(ISNUMBER(FIND("9",A1:B12))))0

Biff

"R. Choate" wrote in message
...
I need to try to cobble together a function nest or whatever I have to
do,
which will tell me if the number 9 exists anywhere within
a range of cells. The 9 could be just one character of a larger number
(i.e. 1496), or it could be just a nine. I know that I could
use filtering or use the find feature with the dialog box, but I need a
formula instead. Also, I just need to know a true or false;
I don't need to know where the nine is or any other info. I just need to
know if it is there. I'm stumped. Any ideas?

Thanks in advance!
--
RMC,CPA

Find a "9" within a range using a function (T/F)
 

My apologies. It was having the wrong range in it that caused the error. Your formula works great, and as I said earlier, thank you
for your help and a working solution to my situation.

Richard

--
RMC,CPA


"R. Choate" wrote in message ...
Hi,

First - thank you. Your solution DOES work. My only comment is that it throws an error if no 9. You probably just assumed I could
take it from there; you're right, I can, and I appreciate it. Yours is a nice, short solution that does work.

Richard

--
RMC,CPA


"Biff" wrote in message ...
Hi!

Try this:

=SUMPRODUCT(--(ISNUMBER(FIND("9",A1:B12))))0

Biff

"R. Choate" wrote in message
...
I need to try to cobble together a function nest or whatever I have to do,
which will tell me if the number 9 exists anywhere within
a range of cells. The 9 could be just one character of a larger number
(i.e. 1496), or it could be just a nine. I know that I could
use filtering or use the find feature with the dialog box, but I need a
formula instead. Also, I just need to know a true or false;
I don't need to know where the nine is or any other info. I just need to
know if it is there. I'm stumped. Any ideas?

Thanks in advance!
--
RMC,CPA

Find a "9" within a range using a function (T/F)
 

Yes, you are right. The error was my fault and I posted an apology for incorrectly stating that it caused an error with no 9
present. The error came from "user mistake". Thanks for checking it and letting me know so I could try it again.

--
RMC,CPA


"Peo Sjoblom" wrote in message ...
It does not throw an error, it returns TRUE of FALSE

--

Regards,

Peo Sjoblom

"R. Choate" wrote in message
...
Hi,

First - thank you. Your solution DOES work. My only comment is that it
throws an error if no 9. You probably just assumed I could
take it from there; you're right, I can, and I appreciate it. Yours is a
nice, short solution that does work.

Richard

--
RMC,CPA


"Biff" wrote in message
...
Hi!

Try this:

=SUMPRODUCT(--(ISNUMBER(FIND("9",A1:B12))))0

Biff

"R. Choate" wrote in message
...
I need to try to cobble together a function nest or whatever I have to
do,
which will tell me if the number 9 exists anywhere within
a range of cells. The 9 could be just one character of a larger number
(i.e. 1496), or it could be just a nine. I know that I could
use filtering or use the find feature with the dialog box, but I need a
formula instead. Also, I just need to know a true or false;
I don't need to know where the nine is or any other info. I just need to
know if it is there. I'm stumped. Any ideas?

Thanks in advance!
--
RMC,CPA

Find a "9" within a range using a function (T/F)
 

Biff wrote...
....
=SUMPRODUCT(--(ISNUMBER(FIND("9",A1:B12))))0
....

If you're willing to put up with an array formula,

=COUNT(FIND(9,range))0

Find a "9" within a range using a function (T/F)
 

Hi Harlan,

You know, I was trying to cobble together an array formula on my own before I decided I was not on the right track. At least I
thought that an array formula would be the path of least resistance...just couldn't make it work.

Thanks, Harlan !!

Richard

--
RMC,CPA


"Harlan Grove" wrote in message oups.com...
Biff wrote...
....
=SUMPRODUCT(--(ISNUMBER(FIND("9",A1:B12))))0
....

If you're willing to put up with an array formula,

=COUNT(FIND(9,range))0