Function to Count Zip Code Digits With Leading Zero
 

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!

Function to Count Zip Code Digits With Leading Zero
 

You would need to enter the data as text. Numbers are stored without
leading zeroes.
You could, of course, use =LEN(TEXT(A2,"00000")) That will return 5 for
integers up to and including 5 digits, but will allow numbers longer than 5
digits to be counted as longer.
--
David Biddulph

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

Function to Count Zip Code Digits With Leading Zero
 

"Daren" wrote:

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

Function to Count Zip Code Digits With Leading Zero
 

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?

--
Rick (MVP - Excel)


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