Len(ZipCode)
 

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?

Len(ZipCode)
 

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

"Rafi" wrote:

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?

Len(ZipCode)
 

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

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

--
Rick (MVP - Excel)


"Rafi" wrote in message
...
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?

Len(ZipCode)
 

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

--
Rick (MVP - Excel)


"Mike H" wrote in message
...
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

"Rafi" wrote:

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?

Len(ZipCode)
 

Rick,

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

Mike

"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


"Mike H" wrote in message
...
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

"Rafi" wrote:

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?

Len(ZipCode)
 

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

"Rick Rothstein" wrote:

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

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

--
Rick (MVP - Excel)


"Rafi" wrote in message
...
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?