Len(ZipCode)

[Rafi]

How do you test the length of a zip code starting with zero (e.g. 07580)?
The column in Excel is formatted as Zip and the page correctly displays the
leading zero however, a test of the length returns 4. Is there a way using
VBA to properly test for a zip code whose first digit is a zero?

 

[Mike H]

Rafi,

I've never encountered a format of 'Zip' is it in 2007?

However, if the leading zero is a format then LEN() won't count it. Why
don't you enter your Zip codes with a leading apostrophe ' then len will work.

Mike

 

[Rick Rothstein]

Test the Text property of the cell, not the value property. For example...

MsgBox Len(Range("A1").Text)

 

[Rick Rothstein]

It is one of the cell format choices under the Special category.

 

[Mike H]

Rick,

Thanks for that, it never appears in my 'special' list because I have UK
selected but it's there under US options.

Mike

 

[Rafi]

Rick - Awesome.....This did the trick

Test the Text property of the cell, not the value property. For example...

MsgBox Len(Range("A1").Text)