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

[R. Choate]

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

[Biff]

Hi!

Try this:

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

Biff

"R. Choate" <[email protected]> wrote in message
news:%[email protected]...
>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
>
>
>

[Gary L Brown]

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

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

HTH,
--
Gary Brown
gary_brown@ge_NOSPAM.com
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
>
>
>
>

[R. Choate]

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" <[email protected]> wrote in message news:%[email protected]...
Hi!

Try this:

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

Biff

"R. Choate" <[email protected]> wrote in message
news:%[email protected]...
>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
>
>
>

[R. Choate]

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" <gary_brown@ge_NOSPAM.com> wrote in message news:[email protected]...
Assuming your range of cells is A1:B5...

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

HTH,
--
Gary Brown
gary_brown@ge_NOSPAM.com
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
>
>
>
>

[Peo Sjoblom]

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

--

Regards,

Peo Sjoblom

"R. Choate" <[email protected]> wrote in message
news:%238tW%[email protected]...
> 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" <[email protected]> wrote in message
news:%[email protected]...
> Hi!
>
> Try this:
>
> =SUMPRODUCT(--(ISNUMBER(FIND("9",A1:B12))))>0
>
> Biff
>
> "R. Choate" <[email protected]> wrote in message
> news:%[email protected]...
> >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
> >
> >
> >
>
>
>

[R. Choate]

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" <[email protected]> wrote in message news:%238tW%[email protected]...
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" <[email protected]> wrote in message news:%[email protected]...
Hi!

Try this:

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

Biff

"R. Choate" <[email protected]> wrote in message
news:%[email protected]...
>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
>
>
>

[R. Choate]

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" <[email protected]> wrote in message news:[email protected]...
It does not throw an error, it returns TRUE of FALSE

--

Regards,

Peo Sjoblom

"R. Choate" <[email protected]> wrote in message
news:%238tW%[email protected]...
> 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" <[email protected]> wrote in message
news:%[email protected]...
> Hi!
>
> Try this:
>
> =SUMPRODUCT(--(ISNUMBER(FIND("9",A1:B12))))>0
>
> Biff
>
> "R. Choate" <[email protected]> wrote in message
> news:%[email protected]...
> >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
> >
> >
> >
>
>
>

[Harlan Grove]

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

[R. Choate]

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" <[email protected]> wrote in message news:[email protected]...
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