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



0
rchoatecpa (136)
11/4/2005 6:13:55 PM
excel 39879 articles. 2 followers. Follow

9 Replies
669 Views

Similar Articles

[PageSpeed] 56

Hi!

Try this:

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

Biff

"R. Choate" <rchoatecpa@NoSpam.com> wrote in message 
news:%23a0fJuW4FHA.268@TK2MSFTNGP10.phx.gbl...
>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
>
>
> 


0
biffinpitt (3172)
11/4/2005 6:46:12 PM
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
> 
> 
> 
> 
0
gary_brown (76)
11/4/2005 6:50:01 PM
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" <biffinpitt@comcast.net> wrote in message news:%233FCJAX4FHA.3292@tk2msftngp13.phx.gbl...
Hi!

Try this:

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

Biff

"R. Choate" <rchoatecpa@NoSpam.com> wrote in message
news:%23a0fJuW4FHA.268@TK2MSFTNGP10.phx.gbl...
>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
>
>
>



0
rchoatecpa (136)
11/4/2005 9:25:05 PM
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:548817A2-8597-4FBB-8A56-D4C58359F83A@microsoft.com...
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
>
>
>
> 


0
rchoatecpa (136)
11/4/2005 9:27:30 PM
It does not throw an error, it returns TRUE of FALSE

-- 

Regards,

Peo Sjoblom

"R. Choate" <rchoatecpa@NoSpam.com> wrote in message
news:%238tW%23YY4FHA.252@TK2MSFTNGP15.phx.gbl...
> 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" <biffinpitt@comcast.net> wrote in message
news:%233FCJAX4FHA.3292@tk2msftngp13.phx.gbl...
> Hi!
>
> Try this:
>
> =SUMPRODUCT(--(ISNUMBER(FIND("9",A1:B12))))>0
>
> Biff
>
> "R. Choate" <rchoatecpa@NoSpam.com> wrote in message
> news:%23a0fJuW4FHA.268@TK2MSFTNGP10.phx.gbl...
> >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
> >
> >
> >
>
>
>


0
terre081 (3244)
11/4/2005 9:58:38 PM
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" <rchoatecpa@NoSpam.com> wrote in message news:%238tW%23YY4FHA.252@TK2MSFTNGP15.phx.gbl...
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" <biffinpitt@comcast.net> wrote in message news:%233FCJAX4FHA.3292@tk2msftngp13.phx.gbl...
Hi!

Try this:

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

Biff

"R. Choate" <rchoatecpa@NoSpam.com> wrote in message
news:%23a0fJuW4FHA.268@TK2MSFTNGP10.phx.gbl...
>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
>
>
>




0
rchoatecpa (136)
11/4/2005 10:18:50 PM
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" <terre08@mvps.org> wrote in message news:eA8tprY4FHA.636@TK2MSFTNGP10.phx.gbl...
It does not throw an error, it returns TRUE of FALSE

-- 

Regards,

Peo Sjoblom

"R. Choate" <rchoatecpa@NoSpam.com> wrote in message
news:%238tW%23YY4FHA.252@TK2MSFTNGP15.phx.gbl...
> 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" <biffinpitt@comcast.net> wrote in message
news:%233FCJAX4FHA.3292@tk2msftngp13.phx.gbl...
> Hi!
>
> Try this:
>
> =SUMPRODUCT(--(ISNUMBER(FIND("9",A1:B12))))>0
>
> Biff
>
> "R. Choate" <rchoatecpa@NoSpam.com> wrote in message
> news:%23a0fJuW4FHA.268@TK2MSFTNGP10.phx.gbl...
> >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
> >
> >
> >
>
>
>



0
rchoatecpa (136)
11/4/2005 10:21:32 PM
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

0
hrlngrv (1990)
11/4/2005 11:08:46 PM
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" <hrlngrv@aol.com> wrote in message news:1131145726.635415.192450@f14g2000cwb.googlegroups.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


0
rchoatecpa (136)
11/5/2005 4:35:23 PM
Reply:

Similar Artilces:

Access attachments don't work anymore
Outlook 2000's security update makes it impossible to receive MS Access .mdb or .mde files. But I need to do this to earn a living as an Access developer! Other than have the sender rename the file there must be a simpler way to override the security patch. Outlook Express let's you turn off this feature but OL 2000 has no such setting. (The feature is basically worthless anyway - any hacker would know you just rename the file, put your hacker code in it and send it. Access will open ANY file, regardless of what you name it!!) See if the information on the following page help...

Formulas don't work in certain cells #2
nope, the cells are formatted as numbers. I simply cannot figure thi out. -Jorda -- kalik24 ----------------------------------------------------------------------- kalik247's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1594 View this thread: http://www.excelforum.com/showthread.php?threadid=27423 Being formatted as numbers doesn't automatically mean they are numbers. Copy an empty cell formatted to General. Select your range of numbers and Paste Special>Add>OK>Esc Gord Dibben Excel MVP On Mon, 1 Nov 2004 11:59:46 -0600, kalik247 <kalik2...

Access 2007 Reports
I'm using an Access 2000 mdb (split ends) in Access 2007. I cannot up- convert because other terminals don't have Office 2007. It works as a database just fine except: When I try to change a (front end) report from the default printer to another specific printer in design view, page setup, it all LOOKS good except it does not save my changes upon return and goes back to the default printer. I never had this problem with earlier versions of Access and the specific printer in questions works fine with other apps and terminals. Any ideas people? Many thanks. JM This is a known prob...

IE8 can't access Microsoft Office site when all other browsers can
When trying to access the Microsoft Office page using IE8 I keep getting the "Internet Explorer cannot display the webpage" message. If I use any other browser (Firefox, Opera, Safari, Chrome) I have no problem accessing the page. This happens whether running in normal or No Add-on's mode. Despite how many people keep complaining about all the problems with IE8, neither the MVP's or Microsoft acknowledge that they exist. Asus P5E Intel E8400 Core2Duo 3.0GHz 4 GB PC2-6400 DDR2 Windows 7 Ultimate (with all the latest updates installed) NIS 2010 (all up to date)...

I can't get rid of 1 "Payment to Send' Reminder-M07
On my Home Page in the Reminder section, I have a "1 Payment to Send" listed there. But there is NO payment to send. I"ve opened up back-up copies as far back as May and the reminder is there, but there is no payment that needs sending. Does anybody have an idea how I can get rid of this Reminder? Just removing the Reminder from Home Page doesn't do it. When you put the Reminder back on the Home Page, the "Payment to Send" is still there. Clicking on the link just takes me to Bill Summary page. ...

Service Pack Installation Issues with GP 9.00.0247.0 on Windows Vi
Greetings, I have installed Great Plains v9.00.0114.0 on Windows Vista. I ran the installation package MicrosoftDynamicsGP-KB923671-v9-ENU, but receive the following messages with result of a failed installation of the Service Pack. Log Name: Application Source: MsiInstaller Date: 10/8/2007 11:50:13 AM Event ID: 11729 Task Category: None Level: Information Keywords: Classic User: ABR\administrator Computer: AD088.abr Description: Product: Microsoft Dynamics GP 9.0 -- Configuration failed. Event Xml: <Event xmlns="http://schemas...

Can't Install IBF for CRM on CRM Server
first i'm install IBF On my CRM v1.2 (it's name testcrm) after that i'm try to install IBF for CRM on my CRM Server but in Step "Publish Metadata" i'm type "http://testcrm:8082" and click next button but it's show message box "The Information Bridge metadata service location is not avaliable" and can't install it. -_-' . but i can access "http://testcrm:8082/IBFWriteService.asmx" and "http://testcrm:8081/IBFReadService.asmx". so how can i install it ? I had the same problem - try using http://localhost:8082 for...

can't change/delete bills and deposits
suddenly one day i realized that when i tried to change a bill, i could click "ok" but nothing would happen. I thought it was because i upgraded to 2004, so i deleted the bills and rescheduled. about 5 of the bills will not delete either. the error message is: "Money could not write to your Money file. The operation could not be performed or another application finished this task before you. Please try again." Now some of the newly scheduled bills will not allow me to change the properties, i click ok but nothing happens when i edit the series. I have repaired ...

Subform doesn't work once added to main form
Hi, I've been searching around looking for some help and can't find anyone that has had quite the same problem. I have a main form called 'Sites' and have added a subform called 'Previous Year Flows' into the main form. I do not enter data into this subform. Instead, data on previous years is drawn through from a table called prev yr flows into this subform. When I open the subform on its own it draws through all the necessary data, but when it is embedded within the main form, none of the actual data is pulled through at all, although the linked fields, Site ID and A...

Emails don't find contacts in restored Outlook
My hard drive burned up, so I replaced it, reinstalled, and imported everything into Outlook. The contacts are there if you click contacts. However, when sending emails, there is no way to connect to my contacts. It's like they are invisible. You never import Outlook data to restore it. You just open the Outlook data file. When you do, you will be able to configure your address book view to display your Contacts the same way as always: http://support.microsoft.com/default.aspx?scid=kb;en-us;287563&Product=ol2002 -- Russ Valentine "Mike Row Soft" <MikeR...

Why can't I send email messages with Trial Outlook 2007?
I have a new HP laptop that came with the 60 day trial of Office'07. I receive email but the send/reply/compose functions are grayed out. I am certain my settings are correct. Why can't I send email? KK What happens when you use the Test Account Settings? What is the exact error message you get? -- Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. ALWAYS post your Outlook version. How to ask a question: http://support.microsoft.com/KB/555375 After furious head scratching, KK asked: | I have a new HP laptop that came with the 60 day...

Access Outlook Add-in for Data Collection & Publishing won't load
I am trying to utilize the Collect Data via e-mail function and keep receiving the following error: "The Microsoft Office Access Outlook Add-in is disabled in Microsoft Office Outlook 2007. To collect data by using e-mail messages in Microsoft Access 2007, verify that this add-in is installed and enabled from within Office Outlook 2007." I have tried to remediate via the Trust Center/Add-in area in Outlook and don't see an add-in for Access (even though the error message says it is "disabled" it is nowhere to be found). I have tried to add it (I believe it is...

Don't get some emails;
Some emails don't come thru. From another computer I can forward messages to my email site but don't receive new messages. Also not getting some emails from various sites. ...

Re:Help with verifying the signature of enveloped -message using x509 certificates.
Re:Help with verifying the signature of enveloped -message using x509 certificates. Hey Friends, I have an xml given by our partner trying to integrating with us and i have their certificate with public key installed in both personal and trusted people folders. when i am using the following code to check the signature of message signed by my certificate i am able to verify the signature but when i am trying to check the signature of the message of our partner using the same following code i am not able to verify the message. the message sent by our partner is right below the code.I wou...

Someone must have asked this before but I can't find it!
Being a relative Excel newcomer I,m not sure of all the terminology so may have used the wrong terms when searching. What I am trying to do is create a worksheet in which a cell shows one of 3 values (LOW,AVERAGE or HIGH) according to the value in the cell to the left of it, which itself is the sum of the 2 preceding cells. To be more specific, cell D6 is Men, E6 is Women and F6 is"=SUM(D6,E6)". I then want G6 to show "LOW" if F6 is less than 5, "AVERAGE" if it is between 5 and 20, and "HIGH" if it is over 20. If anyone understands the question and can h...

Image doesn't show on first page
I have a report that is pulling different images from a file. It is working fine except the image doesn't appear on the first page screen, but does if I print it out. Any ideas why the image is missing on the first screen? ...

Computer won't boot
I have an older computer with a Gigabyte GA-P35-DS3L motherboard that I home-built a few years back. I recently built a new computer and have been keeping the old one alive until I get the new one all checked out and settled in. Yesterday the old computer wouldn't boot. It makes a BEEP about one second long during the boot process but won't go any farther. The drive lights flash and the monitor goes through its first sign of lighting up, after I turn the computer on, but this is as far as it goes. The monitor goes black and the activity lights on the front of the computer fl...

Services won't stop! HELP!
I do offline backups of all our remote Exchange 2003 servers. A batch file is run with a series of "net stop" commands the shuts down all the Exchange services, and then kicks off the backup. I just noticed that the batch file is no longer able to stop most of the services! When i run it manually, it just says "The xyz service is stopping........................" The "....." just keep going and going and the service never stops. What could cause this?? On Thu, 9 Mar 2006 14:41:29 -0500, "jim" <jim@NOSPAM.com> wrote: >I do offline ...

Can't use my MS Office
I have recently purchased the above software package. I have not registered my product, as I have been travelling and it has=20 been less than 3 weeks since purchasing and installing. I am not sure=20 if all of the office programs have been working, as the only ones I have = been using these past 3 weeks are Entourage and MSN Messenger. =20 This morning, I tried to use Word and it froze just after opening the=20 new document and I was only able to enter one letter. Then I tried=20 excel and it froze at the first cell inside the new book. Then I tried = to=20 re-open my previously working E...

Changing Average Cost GP 9.0 Error
When we try to change the average cost information for an item per the documentation, (tools >> utilities >> inventory >> change average cost), we get this message. "Use the Inventory Adjust Costs window to change the cost of one or more reciepts. The average cost of an item is recalculated when teh changes are proceses" We would like to change it without using the Inventory Adjust Costs window because there are no recent purchases and receipts of the particular item in question Would anyone have any suggestions? The average cost is the aver...

Can't archive calendar
I am trying to archive a range of months in Calendar (in Outlook 2003). When I set up File-Archive and select range of dates, the archive process seems to run but the archived Calendar shows no entries and the original Calendar still has the entries that were supposed to be removed. This is happening on at least 2 of my computers with identical setups. What is going wrong? how can I fix it? Thanks! DickSF wrote: > I am trying to archive a range of months in Calendar (in Outlook 2003). When > I set up File-Archive and select range of dates, the archive process seems ...

Why can't I "send as e-mail" in Publisher?
I have created a document using a Newletter e-mail in Publisher, and need to send it as an e-mail (ie embedded in the e-mail) rather than as an attachment. All the literature I've read says I should be able to do this, and that under "Send an Email" under the File menu, there should be two options: "Send as an attachment" and "Send as an e-mail." For me, the second option is missing. Can any one help me out to fix this? Thank you! Depending on your version of Publisher will depend on the exact answer. Publisher requires you to have Outlook or Outlook...

New Task Folders Don't Allow Assignment of Tasks?
I have created a new task folder, called MyTasks and whenever I try to assign a task to someone, Outlook seems to want it to be in the default "Tasks" folder, or it won't update when the asssigned task changes. Is this a limitation? -Greg Yes - that's the way it works. Why do you need another tasks folder? You might want to look into using categories - then you can have everything in one folder & set up filtered views if you like. Greg S wrote: > I have created a new task folder, called MyTasks and > whenever I try to assign a task to someone, Outlook seems &...

I can't copy, paste or save
I cannot copy, paste or save in any document I am working on. In some files I can right click and copy and paste but not on the toolbar, but in most files I cannot do any of these functions at all. The only way I can save is to exit the file and say yes to the save prompt. Any input would be appreciated, thank you. I think I'd try this: Use windows|find to find *.xlb (search in hidden folders for hidden files, too). Rename it/them to *.xlbOLD. Restart excel and try it out. If it worked, then delete those *.xlbOLD files. if it didn't work, then rename them to *.xlb. *.xlb is wh...

Visio 2003 tril won't let me do anything
Is there something I need to do to make this work? I can't create new docs or make any changes to old docs. emmm... I thing you not yet 'activate' you product. That's why you can't do anything except just open the file. Are you? To 'Activate Product' go to 'Help' menu then select it. Follow the instruction... "Suzie" <SB29@mdahospital.com> wrote in message news:uAUXYvX8DHA.1356@tk2msftngp13.phx.gbl... > Is there something I need to do to make this work? I can't create new docs > or make any changes to old docs. > > ...