zip code conditional formatting

[greyhound]

I have a column with zip codes, some 5 characters, some 9 characters. I need to write an if statment in conditinal formatting that will insert a dash after 5 characters if the length is 9 characters. This is my attempt but does not work:

=if(len(text) > 5, #####, #####-####)

Many thanks, greyhound

 

[FryGirl]

Could something like this work for you?

=IF(LEN(A1)>5,TEXT(A1,"00000-0000"),TEXT(A1,"00000"))

 

[capparfrank]

I think that in conditional formatting, you won't use an 'if' statment. Simply provide the condition to evaluate in formula.

If you want to create an if/else behavior, you need to create two rules.

so:

rule 1 formula:
= LEN($A$1) > 5

rule 1 format:
#####-####

rule 2 formula:
= LEN($A$1) <= 5

rule 2 format:
#####

 

[FryGirl]

Sorry I did miss the part about conditional formatting

 

[greyhound]

Thanks, worked just fine. Thanks to Gail also.