Function to Count Zip Code Digits With Leading Zero

[Daren]

Hello,

I have a zip code as 06110 formatted as zip code. I used LEN on that zip
code and it returns a 4 because it's not reading the leading zero. Is there
a function that will return a value of 5 for a zip code with a leading zero
and still be able to return a value of 5 for zip codes that have 5 digits?
Thanks!

 

[Billy Liddel]

Hello,

I have a zip code as 06110 formatted as zip code. I used LEN on that zip
code and it returns a 4 because it's not reading the leading zero. Is there
a function that will return a value of 5 for a zip code with a leading zero
and still be able to return a value of 5 for zip codes that have 5 digits?
Thanks!

Daren

The problem with a zip code format is just that, it changes the way a number
looks so len will ignore a leading zero and LEN(12.00) will always be 2.

You can convert the number to text with a helper column and then copy and
paste special the values over the formulas and then cut and past them into
the zip codes.

if your zip is in A6 use; =IF(LEFT(A6,1)="0",A6,"0"&A6) and copy down.

Regards
Peter Atherton

 

[Rick Rothstein]

If you have the cell formatted as Zip Code, why are you checking its length
at all? Wouldn't all your zip codes have to be 5 characters long with that
format? Or are you putting something else besides zip codes in a cell
formatted as Zip Code? If that is what you are doing, then what other
entries can there be?