Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • Create Scatter Chart in Excel
  • Get column index in Excel Table
  • Excel Line Chart
  • How To Filter Data in Excel
  • How to do a t-Test in Excel?

References

  • How to get last row in numeric data in Excel
  • Excel Advanced Lookup using Index and Match Functions
  • How to get relative row numbers in a range in Excel
  • Offset in Excel
  • Vlookup Examples in Excel

Data Validations

  • Excel Data validation only dates between
  • Excel Data validation no punctuation
  • Excel Data validation allow weekday only
  • Excel Data validation allow uppercase only
  • Data validation must not exist in list

Highlight numbers that include symbols in Excel

by

This tutorial shows how to Highlight numbers that include symbols in Excel using the example below;

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

Post navigation

Previous Post:

COSH function: Description, Usage, Syntax, Examples and Explanation

Next Post:

Excel Data validation require unique number

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • How to return blank in place of #DIV/0! error in Excel
  • IFERROR function: Description, Usage, Syntax, Examples and Explanation
  • IF function: Description, Usage, Syntax, Examples and Explanation
  • NOT function: Description, Usage, Syntax, Examples and Explanation
  • Not Equal To ‘<>‘ operator in Excel

Date Time

  • MONTH function: Description, Usage, Syntax, Examples and Explanation
  • Check If Two Dates are same month in Excel
  • Calculate series of dates by workdays in Excel
  • Get month name from date in Excel
  • Add business days to date in Excel

Grouping

  • Group times into unequal buckets in Excel
  • How to randomly assign people to groups in Excel
  • Running count group by n size in Excel
  • Group numbers with VLOOKUP in Excel
  • Map text to numbers in Excel

General

  • Sum by group in Excel
  • Share Excel data with Word documents
  • Split Cell Content Using Text to Columns in Excel
  • With vs Without Array Formula in Excel
  • How to calculate percentage discount in Excel
© 2025 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning