Conditional Formatting cells that do not contain letters

[rjv7888]

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>

 

[ChrisOswald]

try

=ISNUMBER(1*REPLACE(A1,FIND("-",A1),1,""))

for your conditional formatting formula.

 

[Special-K99]

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]

Hey guys, thanks for the quick replies. Everything is working great. I appreciate your time.
Best of luck,
Raul

 

[NormanS]

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?