Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional formating | Excel Discussion (Misc queries) | |||
Conditional Formating | Excel Discussion (Misc queries) | |||
Conditional Formating | Excel Worksheet Functions | |||
Conditional Formating | Excel Worksheet Functions | |||
Install dates formating using conditional formating? | Excel Discussion (Misc queries) |