conditional formating with len
 

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

conditional formating with len
 

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.




On Feb 7, 1:22 pm, doyree wrote:
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

conditional formating with len
 

Correction...

my post should say...

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


On Feb 7, 1:34 pm, Tim879 wrote:
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.

On Feb 7, 1:22 pm, doyree wrote:

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

conditional formating with len
 

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.


--
Kevin Backmann


"doyree" wrote:

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

conditional formating with len
 

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

"doyree" wrote:

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

conditional formating with len
 

CFFormula is =LEN($A1)<5 Format to red


Gord Dibben MS Excel MVP

On Thu, 7 Feb 2008 10:22:05 -0800, doyree
wrote:

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

conditional formating with len
 

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)

--
Biff
Microsoft Excel MVP


"doyree" wrote in message
...
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

conditional formating with len
 

tried all formulas
yours did the magic!!

thanks!!!!!

"Gord Dibben" wrote:

CFFormula is =LEN($A1)<5 Format to red


Gord Dibben MS Excel MVP

On Thu, 7 Feb 2008 10:22:05 -0800, doyree
wrote:

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