Conditional Formatting cells that do not contain letters

rjv7888

New Member
Joined
Jul 14, 2010
Messages
26
Hi,
I am trying to highlight all of the cells in a column that do not contain letters, or only contain numbers. The yellow and red cells follow separate rules, but the entries below the yellow ones are the values that I am looking to identify with whatever method available.

Thanks,
Raul


Here is my data (the list is much longer, but the pattern repeats):

<table style="border-collapse: collapse; width: 72pt;" border="0" cellpadding="0" cellspacing="0" width="96"><col style="width: 72pt;" width="96"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 72pt; font-size: 11pt; color: rgb(156, 0, 6); font-weight: 400; text-decoration: none; font-family: Calibri; background: none repeat scroll 0% 0% yellow;" width="96" height="20">227-634290D1</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">227-136221
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">227-162936DG</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt; font-size: 11pt; color: rgb(156, 0, 6); font-weight: 400; text-decoration: none; font-family: Calibri; background: none repeat scroll 0% 0% rgb(255, 199, 206);" height="20">227-634092CS</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt; font-size: 11pt; color: rgb(156, 0, 6); font-weight: 400; text-decoration: none; font-family: Calibri; background: none repeat scroll 0% 0% yellow;" height="20">227-634296D1</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">227-20357</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">227-172956HT</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">227-539328GR</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">227-68874BT</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">227-583848CA</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">227-539346DA</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt; font-size: 11pt; color: rgb(156, 0, 6); font-weight: 400; text-decoration: none; font-family: Calibri; background: none repeat scroll 0% 0% rgb(255, 199, 206);" height="20">227-634092CS</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt; font-size: 11pt; color: rgb(156, 0, 6); font-weight: 400; text-decoration: none; font-family: Calibri; background: none repeat scroll 0% 0% yellow;" height="20">227-634290D1</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">227-136007</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">227-137226HP</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt; font-size: 11pt; color: rgb(156, 0, 6); font-weight: 400; text-decoration: none; font-family: Calibri; background: none repeat scroll 0% 0% rgb(255, 199, 206);" height="20">227-634092CS</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt; font-size: 11pt; color: rgb(156, 0, 6); font-weight: 400; text-decoration: none; font-family: Calibri; background: none repeat scroll 0% 0% yellow;" height="20">227-634290D1</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">227-136187</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">227-162960DG</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt; font-size: 11pt; color: rgb(156, 0, 6); font-weight: 400; text-decoration: none; font-family: Calibri; background: none repeat scroll 0% 0% rgb(255, 199, 206);" height="20">227-634092CS
</td> </tr> </tbody></table>
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

ChrisOswald

Active Member
Joined
Jan 19, 2010
Messages
454
try
Code:
=ISNUMBER(1*REPLACE(A1,FIND("-",A1),1,""))

for your conditional formatting formula.
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
Try this

=(ISNUMBER(SUBSTITUTE(A1,"-","")+0)=TRUE)
and format as required

It should highlight only numeric fields, ie

227-136187

but not

227-162960DG
 

rjv7888

New Member
Joined
Jul 14, 2010
Messages
26
Hey guys, thanks for the quick replies. Everything is working great. I appreciate your time.
Best of luck,
Raul
 

NormanS

New Member
Joined
Mar 4, 2011
Messages
1
Hi guys,

I am hoping to piggyback off of the first question.

I want to use conditional formatting to highlight cells that contain:

color red for: 3 letters in a row followed by two numbers
color yellow: for one letter-3 numbers-one letter

for example:
ITO32 = Red
C231J= Yellow

is this doable?
 

Watch MrExcel Video

Forum statistics

Threads
1,118,861
Messages
5,574,703
Members
412,612
Latest member
Shotokan
Top