Function to Count Zip Code Digits With Leading Zero

  • Thread starter Thread starter Daren
  • Start date Start date
D

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!
 
Daren said:
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!
Click to expand...

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
 
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?
 
Back
Top