Excel formula: Highlight numbers that include symbols

Generic formula

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

Explanation

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.

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

See 40 examples of conditional formatting with formulas

Author

Dave Bruns

Related formulas

Highlight values greater than

Note: Excel contains built-in "presets" for highlighting values above / below / equal to certain values, but if you want more flexibility you can apply conditional formatting with your own formula as explained in this article. If you want to...

Highlight values between

Note: Excel contains many built-in "presets" for highlighting values above / below / between / equal to certain values, but if you want more flexibility you can apply conditional formatting using your own formula as explained in this article. If you...

Related functions

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

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

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.