conditional formating with len

[doyree]

hello,

i have a question with conditional formating. please help.

a
00607
01505
132
12345

i wanted that if cell box contained less than 5 characters, it is fill with
red.
above is all digits stores in text. i made it text since some values have 0
in front.

i went to conditional formating and did
cell value is, not equal to, ="LEN(5)"
then fill box with red
i also tried ="LENB(10)" but that didn't work.

could you please help?

thank you

 

[Tim879]

You need to use a conditional stmt in the conditional format...

try putting this in the conditional format box...
=if(len(a1)=5,0,1)

this will return true if A1 does not = 5, since it's true it will
trigger your conditional format.

 

[Tim879]

Correction...

my post should say...

this will return true if THE LENGTH OF A1 does
not = 5, since it's true it will

 

[Mike H]

You were very close. Try this. Select the cells you want to format then
Format|Conditional Format|Formula is

Paste this in
=LEN(A1)<5
Choose a colour and click OK
Note you must change A1 to the topmost selected cell.

Mike

 

[Kevin B]

Select the cells to format and do the following:

Click FORMAT in the menu and select CONDITIONAL FORMATTING

Change CELL IS in condition 1 to FORMULA IS

In the formula field enter the following as a formula (No quotes),
substituting A1 in the formula with the starting cell in your selection range:
=LEN(A1)<5

Click the FORMAT button and click the PATTERNS tab, click the color flavor
of your choosing and click the OK command button to exit formatting and then
click the OK command button to exit conditional formatting.

 

[Gord Dibben]

CF>Formula is =LEN($A1)<5 Format to red


Gord Dibben MS Excel MVP

 

[T. Valko]

You might want to exclude empty cells (if there are any):

Assume the range in question is A1:A5
Select the range A1:A5
Formula Is: =AND(A1<>"",LEN(A1)<5)

 

[doyree]

tried all formulas
yours did the magic!!

thanks!!!!!