Need to make Excel 'isnumber search' formula case sensitive

Andreamary
Andreamary used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solutions Only
Professor JMicrosoft Excel Expert
Top Expert 2014

Commented:
for case sensitive replace the SEARCH function with FIND function
Rob HensonFinance Analyst
Commented:
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
Microsoft Excel Expert
Top Expert 2014
Commented:
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
Ensure you’re charging the right price for your IT

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

Find out now!
Andreamary

Author

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
Rob HensonFinance Analyst

Commented:
Glad to be of help.
Professor JMicrosoft Excel Expert
Top Expert 2014

Commented:
You're welcome

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial