Microsoft Office
Microsoft Office

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: 2017-11-02

Who is Participating?

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

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium. Start your 7-day free trial.

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.

LVL 27

ProfessorJimJamMicrosoft Excel ExpertCommented: 2017-11-02

for case sensitive replace the SEARCH function with FIND function

LVL 36

Rob HensonFinance AnalystCommented: 2017-11-02

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.

LVL 27

ProfessorJimJamMicrosoft Excel ExpertCommented: 2017-11-02

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

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

AndreamaryAuthor Commented: 2017-11-02

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