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

Quick Start | More Examples | Troubleshooting | Training

Author

Dave Bruns

Related formulas

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

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

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.