Highlight numbers that include symbols

Highlight numbers that include symbols

Generic formula 

=IF(ISNUMBER(B4),B4<input,IF(LEFT(B4)="<",(MID(B4,2,LEN(B4))+0)<input))

Related formulas 

Highlight values greater than

Highlight values between

Explanation

To highlight numbers less than a certain value, including numbers entered as text like "<9",></9",><10",></10",>named range for cell G2.

How this formula works

The formula first uses the ISNUMBER function to test if the value is a number, and applies a simple logical if so:

=IF(ISNUMBER(B4)

For any number less than the value in "input", the formula will return TRUE and the conditional formatting will be applied.

However, if the value is not a number, the formula then checks if the first character is a less than symbol (<)></)>

IF(LEFT(B4)="<"

If so, the MID function is used to extract everything after the symbol:

MID(B4,2,LEN(B4)

Technically, the LEN function returns a number 1 greater than we need, since it includes the "<"></">

The result of MID is always text so the formula adds zero to force a Excel to convert the text to a number. This number is then compared to the value from "input". 

Highlight numbers that include symbols

Generic formula 

=IF(ISNUMBER(B4),B4<input,IF(LEFT(B4)="<",(MID(B4,2,LEN(B4))+0)<input))

Related formulas 

Highlight values greater than

Highlight values between

Explanation

To highlight numbers less than a certain value, including numbers entered as text like "<9",></9",><10",></10",>named range for cell G2.

How this formula works

The formula first uses the ISNUMBER function to test if the value is a number, and applies a simple logical if so:

=IF(ISNUMBER(B4)

For any number less than the value in "input", the formula will return TRUE and the conditional formatting will be applied.

However, if the value is not a number, the formula then checks if the first character is a less than symbol (<)></)>

IF(LEFT(B4)="<"

If so, the MID function is used to extract everything after the symbol:

MID(B4,2,LEN(B4)

Technically, the LEN function returns a number 1 greater than we need, since it includes the "<"></">

The result of MID is always text so the formula adds zero to force a Excel to convert the text to a number. This number is then compared to the value from "input".