Exceljet

Quick, clean, and to the point

Highlight numbers that include symbols

Excel formula: Highlight numbers that include symbols
Generic formula 
=IF(ISNUMBER(B4),B4<input,IF(LEFT(B4)="<",(MID(B4,2,LEN(B4))+0)<input))
Summary 

To highlight numbers less than a certain value, including numbers entered as text like "<9", "<10", etc., you can use conditional formatting with a formula strips the symbols as needed and handles the result as a number. In the example shown, "input" is a named range for cell G2.

Explanation 

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 (<) using the LEFT function:

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 "<" symbol as well. If this bothers you, feel free to subtract 1.

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".

Author 
Dave Bruns
Related formulas 
Excel formula: Highlight values greater than
Highlight values greater than
When you use a formula to apply conditional formatting, the formula is evaluated relative to the active cell in the selection at the time the rule is created. So, in this case the formula =B4>100 is evaluated for each of the 40 cells in B4:G11...
Excel formula: Highlight values between
Highlight values between
When you use a formula to apply conditional formatting, the formula is evaluated for each cell in the range, relative to the active cell in the selection at the time the rule is created. So, in this case, if you apply the rule to B4:G11, with B4 as...
Related functions 
Excel ISNUMBER function
Excel ISNUMBER Function

The Excel ISNUMBER function returns TRUE when a cell contains a number, and FALSE if not. You can use ISNUMBER to check that a cell contains a numeric value, or that the result of another function is a number.

Excel MID function
Excel MID Function

The Excel MID function extracts a given number of characters from the middle of a supplied text string. For example, =MID("apple",2,3) returns "ppl".

See also 
Cool things you can do with conditional formatting
Conditional formatting with formulas (10 examples)

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

500 Formulas | 101 Functions

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.