Trying to conditional format a spreadsheet containing values with less-than symbol

Question

I am having a real tough time here writing a conditional formatting formula for my spreadsheet.

I have a table of concentrations from a lab analysis, and various results have come back as below a detection limit (e.g. result returned as "<0.0001"). I want a conditional formatting rule to identify all results where the detection limit is higher than the specific threshold value I am comparing the results against (e.g. E14).

What I need the formatting rule/formula to do is ask these two questions, and only format if both are answered YES/TRUE:

1. Does M14 contain a less than symbol "<"?

2. (ignoring the less than value "<") is M14 value more than E14

(an example in the screenshot would be N17 where the rule should answer both queries with YES, and therefore should format the cell)

Hopefully for a bit of clarity, I've included a screenshot. I would have though this could be solved with an AND and SUBSTITUTE function, but I cannot seem to get it to work.

This is what I have tried so far:

=((SUBSTITUTE(M14,"<",""))AND(14>=E14,M14=""<*""))

Any help on how I can iron out this formatting formula would be so great. The irritating thing is, I have done this before about 2 years ago successfully, and now can't remember (or find the original sheet) how to do it.

Answer 1

=AND(ISNUMBER(SEARCH("<",M14)),VALUE(SUBSTITUTE(M14,"<",""))>E14)

This only returns true if M14 contains a < character and the value in M14 (with any < characters removed) is greater than the value in E14.