Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • How to use Excel MMULT Function
  • Basic INDEX MATCH approximate in Excel
  • How to use Excel LOOKUP Function
  • Lookup entire row in Excel
  • How to get address of first cell in range in Excel

Data Analysis Examples

  • How to create running total in an Excel Table
  • Everything about Charts in Excel
  • Conditional Formatting Icon Sets Examples in Excel
  • Remove Duplicates Example in Excel
  • How to count table rows in Excel

Data Validation Examples

  • Data validation must not exist in list
  • Excel Data validation whole percentage only
  • Excel Data validation allow weekday only
  • Excel Data validation specific characters only
  • Excel Data validation must contain specific text

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.

Worked Example:   Extract middle name from full name -- Manipulating NAMES in Excel

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.

Worked Example:   Excel Data validation must not contain

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.

Worked Example:   Highlight duplicate values in Excel

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:

Highlight rows with blank cells in Excel

Next Post:

Highlight row and column intersection exact match in Excel

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
  • How to count specific words in a cell in Excel
  • How to count specific characters in a cell in Excel
  • Get first name from name with comma — Manipulating NAMES in Excel
  • REPLACE, REPLACEB functions: Description, Usage, Syntax, Examples and Explanation
  • T function: Description, Usage, Syntax, Examples and Explanation
  • Display Days until expiration date in Excel
  • Get day name from date in Excel
  • Steps to create Dynamic calendar grid in Excel
  • WORKDAY.INTL function: Description, Usage, Syntax, Examples and Explanation
  • Display the current date and time in Excel
  • AMORDEGRC function: Description, Usage, Syntax, Examples and Explanation
  • Calculate periods for annuity in Excel
  • CUMIPMT function: Description, Usage, Syntax, Examples and Explanation
  • COUPDAYBS function: Description, Usage, Syntax, Examples and Explanation
  • Calculate cumulative loan principal payments in Excel
© 2022 xlsoffice . All Right Reserved. | Teal Smiles