How to apply Conditional Formatting in a cell before a particular character

Question asked by user @ Excel forum:-

How to apply Conditional Formatting in a cell before a particular character in Microsoft Excel ?

 

I am trying to highlight cells that contain an X number of characters before the - sign. For e.g.. lets say I have a list like this:

12-2929
0993-2929
00992-2939
10-2929

From the above list I would like to highlight cells that contain 2 characters before the dash, i.e., 12-2929 and 10-2929. I was wondering, what would be the best way of doing this via conditional formatting or using the Len formula in combination with another formula?

image 1

 

  • Select the range & press ALT + O + D
  • New Formatting Rule window will be pop up

image 2

  • Select “Use a formula to determine which cells to format”

image 3

  • Enter the formula as =FIND("-",A1)-1=2

image 4

  • Click on Format & fill the color of your choice.
  • You will get the desired result.

image 5

 

Main Page

Conditional Format Based on Dates

Find Occurrence of Text in a Column

How to Highlight a row on the basis of Cell

Compare 2 Columns and Return Fill Red if is different

How to check the row and then highlight the first cell of the row

Highlight Cells Tomorrow Excluding Weekend

Conditional Formatting to Mark Dates on a Calendar

How to apply Conditional Formatting  in a Cell before a Particular Character

Highlight the Top 10 Sales through Conditional Formatting

Conditional Formatting for Pivot Tables

Conditional Format Between First and Last Non-Blank Cells

PDF

Excel

 

 

 

 

Leave a Reply

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

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube