We help IT Professionals succeed at work.

Need to make Excel 'isnumber search' formula case sensitive

Andreamary
Andreamary asked
on
277 Views
Last Modified: 2018-02-02
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

Professor JMicrosoft Excel Expert
CERTIFIED EXPERT
Top Expert 2014

Commented:
for case sensitive replace the SEARCH function with FIND function
Rob HensonFinance Analyst
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Microsoft Excel Expert
CERTIFIED EXPERT
Top Expert 2014
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

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
CERTIFIED EXPERT

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

Commented:
You're welcome

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions