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?
 
ProfessorJimJamConnect With a Mentor 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
0
 
ProfessorJimJamCommented:
for case sensitive replace the SEARCH function with FIND function
2
 
Rob HensonConnect With a Mentor Finance 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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
ProfessorJimJamCommented:
You're welcome
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.