1

Using Excel 2013 I'm looking to apply a conditional formatting rule to all cells in a range of a column that do not contain SD followed by two numbers, so an example would be SD01 or SD32. The column of interest is K.

I've thought I would deal with the two characters first then figure out how to add the two digits.

I came up with a formula that ignores blank cells, then highlights the ones that have SD.

The first formula I tried that highlights the correct ones (the ones with SD):

=IFERROR(IF(ISBLANK(K12),"",(IF(SEARCH("SD",K12,1),1,0))),"")

I thought reversing the results of the SEARCH formula would have solved that problem:

=IFERROR(IF(ISBLANK(K12),"",(IF(SEARCH("SD",K12,1),1,""))),1)

If this is simpler with Office 2019, please mention it.

1 Answer 1

2

This may do what you are looking for:

=NOT(OR(IFERROR(SEARCH("SD",K12,1),0),K12=""))

enter image description here


For the more involved request (regarding the numbers after the "SD"):

=NOT(OR(IFERROR(VALUE(MID(K12,SEARCH("SD",K12,1)+2,2)),0),K12=""))

enter image description here


If values with only one digit after SD (e.g. SD1) should also be formatted, I would suggest this:

=NOT(OR(AND(IFERROR(ISNUMBER(VALUE(MID(K12,SEARCH("SD",K12,1)+2,1))),0),IFERROR(ISNUMBER(VALUE(MID(K12,SEARCH("SD",K12,1)+3,1))),0)),K12=""))

enter image description here

3
  • Thanks that made a huge difference, I'll keep using NOT in mind in the future.
    – BradR
    Mar 16, 2021 at 13:32
  • Thanks that worked perfectly. Sorry for the slow response. In a meeting so... I applied it, it worked. I'll double back later to review it so I understand it.
    – BradR
    Mar 16, 2021 at 15:05
  • Thanks again, I just reviewed the formula; you are set up to find the first SD then using MID to take first value afterwards and confirm it was a number and part to the 3rd value afterwards and confirm it is a number. Something I hadn't thought of.
    – BradR
    Mar 16, 2021 at 17:20

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .