Need to make Excel 'isnumber search' formula case sensitive

I have a conditional formatting formula that changes a cell color in Column H based on if the cell in Column F contains "NO":

Format of Column F data:
AA–DD MMM YY–YES or  AA–DD MMM YY–NO

Examples of Column F data:
MC–13 Oct 17–YES
MC–14 Oct 17–YES
MC–31 Oct 17–NO

Conditional Format formula:
=ISNUMBER(SEARCH("*NO*",$F2))

Open in new window


This formula has been working fine until we hit November, whereby the month also contains "No" so the formula is no longer working properly:
MC–01 Nov 17–YES
MC–01 Nov 17–NO

I need to revise the conditional format formula so it is case sensitive, so it only detects 'NO' in all capital letters:
=ISNUMBER(SEARCH("*NO*",$F2))  'this formula needs to be case sensitive, detecting only 'NO' and not 'No'

Open in new window


I have attached a sample spreadsheet.

Thanks!
Andrea
CaseSensitive_Search_IsNumber_EE.xlsm
AndreamaryAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ProfessorJimJamMicrosoft Excel ExpertCommented:
for case sensitive replace the SEARCH function with FIND function
2
Rob HensonFinance AnalystCommented:
If the cell contents will always be in the format that the contents end with Yes or No then you don't need to use the SEARCH or FIND function.

See screenshot below showing the format.
Conditional Format
0
ProfessorJimJamMicrosoft Excel ExpertCommented:
i modified the conditonal formatting on sheet  WO_List

see attached.

also with FIND you do not need to use asterisks

just put  =ISNUMBER(FIND("NO",$F2))
CaseSensitive_Search_IsNumber_EE.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

AndreamaryAuthor Commented:
Thanks to you both for responding so quickly with solutions! While Rob's solution worked for this specific instance, I also appreciated the versatility of Jim's formula in case the format in Column F changes so that 'NO' and 'YES' are no longer at the end of the string (a very likely scenario in the near future, actually), so thought it best to share the points. Hope that works for everyone! :-)

Best regards,
Andrea
0
Rob HensonFinance AnalystCommented:
Glad to be of help.
0
ProfessorJimJamMicrosoft Excel ExpertCommented:
You're welcome
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.