How to conditional format if first value is a letter and not a number

Michelle0918

Board Regular
Joined
Nov 14, 2013
Messages
66
Hello,

I have column M that has a Left String formula in it to pull some 4 digit numbers : =LEFT(I43,4)

I would like to conditional format all of column M so that if it pulls anything that starts with a number value that it is basically a white or no-fill color background, but if it pulls anything that starts with a letter and not a number, it formats as a Red background so that I can check & balance my work.

Thanks!
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Try

=ISERROR(LEFT(I43,1)+0)


Although logicallly this is saying if the first character is NOT a number..
Not necessarily that the first character IS a letter..
So it would be TRUE if the first character is a symbol like $ or & or # etc...(anything other than 0 through 9)
 

Michelle0918

Board Regular
Joined
Nov 14, 2013
Messages
66
can I adjust it so that if the cell is blank then it will not be highlighted in Red?
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061

ADVERTISEMENT

Try

=AND(I43<>"",ISERROR(LEFT(I43,1)+0))
 

Michelle0918

Board Regular
Joined
Nov 14, 2013
Messages
66
hrmm..didn't work..it highlighted my cell Red when even though it started with a number value
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061

ADVERTISEMENT

You better look more closely...

Perhaps the value is actually beginning with a space ?
Or
A previousely created conditional format is still there messing it up.
 

Michelle0918

Board Regular
Joined
Nov 14, 2013
Messages
66
ok fixed!..i had to adjust some minor mistakes in the cell number reference.
Thanks!
 

Forum statistics

Threads
1,141,705
Messages
5,707,973
Members
421,539
Latest member
zuniBM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top