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?

Solutions

LVL 27

ProfessorJimJamConnect With a Mentor Commented: 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

LVL 27

ProfessorJimJamCommented: 2017-11-02

for case sensitive replace the SEARCH function with FIND function

LVL 35

Rob HensonConnect With a Mentor Finance 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.

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