FormatCellsSpecialZip Code
 

Every day I receive a list of Zip Codes covering Sales Leads. The as-received
format includes regular 5 digit zips, 5+4 zips, and 5 digit zips with the
leading zeros omitted, but I need to change it to 5 digit to process further.

Routinely, I use the command "FormatCellsSpecialZip Code" to add the
leading zeros back. But the command seems unable to convert a '5+4' format to
a simple 5 digit format, and I end up having to delete the hypen and the last
4 digits manually. Am I missing a trick?

Mike

FormatCellsSpecialZip Code
 

Your 5+4 ZIPs were presumably stored as text, rather than as numbers, so
formatting the cell has no effect.
--
David Biddulph

"MichaelRobert" wrote in message
...
Every day I receive a list of Zip Codes covering Sales Leads. The
as-received
format includes regular 5 digit zips, 5+4 zips, and 5 digit zips with the
leading zeros omitted, but I need to change it to 5 digit to process
further.

Routinely, I use the command "FormatCellsSpecialZip Code" to add the
leading zeros back. But the command seems unable to convert a '5+4' format
to
a simple 5 digit format, and I end up having to delete the hypen and the
last
4 digits manually. Am I missing a trick?

Mike

FormatCellsSpecialZip Code
 

David:

Thanks for the suggestion but I don't think that 'stored as text' is the
problem. The FormatCellsSpecialZip Code command has no trouble adding
dropped leading zeroes. And it will not convert 5+4 ZIPs to 5 ZIPS after I
have formatted to 'number'.

Is there anyhting else?
M

"David Biddulph" wrote:

Your 5+4 ZIPs were presumably stored as text, rather than as numbers, so
formatting the cell has no effect.
--
David Biddulph

"MichaelRobert" wrote in message
...
Every day I receive a list of Zip Codes covering Sales Leads. The
as-received
format includes regular 5 digit zips, 5+4 zips, and 5 digit zips with the
leading zeros omitted, but I need to change it to 5 digit to process
further.

Routinely, I use the command "FormatCellsSpecialZip Code" to add the
leading zeros back. But the command seems unable to convert a '5+4' format
to
a simple 5 digit format, and I end up having to delete the hypen and the
last
4 digits manually. Am I missing a trick?

Mike

FormatCellsSpecialZip Code
 

What do =ISTEXT(A2) and =ISNUMBER(A2) say for your 5+4 ZIPs? I would still
suspect that you've got text rather than numbers.

If you do have text, then =LEFT(A2,5) ought to work to convert them to 5
ZIPs.
If they are numbers, then =LEFT(TEXT(A2,"000000000"),5) should do it (and on
a quick test it looks as if that would probably work with the text ones
too).
--
David Biddulph

"MichaelRobert" wrote in message
...
David:

Thanks for the suggestion but I don't think that 'stored as text' is the
problem. The FormatCellsSpecialZip Code command has no trouble adding
dropped leading zeroes. And it will not convert 5+4 ZIPs to 5 ZIPS after I
have formatted to 'number'.

Is there anyhting else?
M

"David Biddulph" wrote:

Your 5+4 ZIPs were presumably stored as text, rather than as numbers, so
formatting the cell has no effect.
--
David Biddulph

"MichaelRobert" wrote in
message
...
Every day I receive a list of Zip Codes covering Sales Leads. The
as-received
format includes regular 5 digit zips, 5+4 zips, and 5 digit zips with
the
leading zeros omitted, but I need to change it to 5 digit to process
further.

Routinely, I use the command "FormatCellsSpecialZip Code" to add the
leading zeros back. But the command seems unable to convert a '5+4'
format
to
a simple 5 digit format, and I end up having to delete the hypen and
the
last
4 digits manually. Am I missing a trick?

Mike

FormatCellsSpecialZip Code
 

Thanks, David.

ISTEXT returns FALSE; ISNUMBER returns TRUE.

The LEFT command works well - thank you.

Btw, even if I start with a fresh worksheet, and enter numbers to create a
5-4 ZIP (like 32034-1234) the FormatCellsSpecialZip Code command does not
convert the entry to 5 digits. Should it?

Mike

"David Biddulph" wrote:

What do =ISTEXT(A2) and =ISNUMBER(A2) say for your 5+4 ZIPs? I would still
suspect that you've got text rather than numbers.

If you do have text, then =LEFT(A2,5) ought to work to convert them to 5
ZIPs.
If they are numbers, then =LEFT(TEXT(A2,"000000000"),5) should do it (and on
a quick test it looks as if that would probably work with the text ones
too).
--
David Biddulph

"MichaelRobert" wrote in message
...
David:

Thanks for the suggestion but I don't think that 'stored as text' is the
problem. The FormatCellsSpecialZip Code command has no trouble adding
dropped leading zeroes. And it will not convert 5+4 ZIPs to 5 ZIPS after I
have formatted to 'number'.

Is there anyhting else?
M

"David Biddulph" wrote:

Your 5+4 ZIPs were presumably stored as text, rather than as numbers, so
formatting the cell has no effect.
--
David Biddulph

"MichaelRobert" wrote in
message
...
Every day I receive a list of Zip Codes covering Sales Leads. The
as-received
format includes regular 5 digit zips, 5+4 zips, and 5 digit zips with
the
leading zeros omitted, but I need to change it to 5 digit to process
further.

Routinely, I use the command "FormatCellsSpecialZip Code" to add the
leading zeros back. But the command seems unable to convert a '5+4'
format
to
a simple 5 digit format, and I end up having to delete the hypen and
the
last
4 digits manually. Am I missing a trick?

Mike

FormatCellsSpecialZip Code
 

ISTEXT is going return TRUE to the 5-4 zip code. If you enter
00000-1111, Excel assumes its a text entry, otherwise there'd be
confusion on whether it was a zip or a subtraction formula. Strings like
12345 are assumed to be numbers, unless you tell it otherwise, and you
can format numbers to have leading and trailing zeroes, but you can't
format text like that.

My wish is that people would treat zip codes, telephone numbers, serial
numbers (e.g part numbers) as they would text, because nobody is going
to do any calculations to those, except as a text character string.

Maybe clearer, maybe not. Hope it is.

Beege



MichaelRobert wrote:
Thanks, David.

ISTEXT returns FALSE; ISNUMBER returns TRUE.

The LEFT command works well - thank you.

Btw, even if I start with a fresh worksheet, and enter numbers to create a
5-4 ZIP (like 32034-1234) the FormatCellsSpecialZip Code command does not
convert the entry to 5 digits. Should it?

Mike

"David Biddulph" wrote:

What do =ISTEXT(A2) and =ISNUMBER(A2) say for your 5+4 ZIPs? I would still
suspect that you've got text rather than numbers.

If you do have text, then =LEFT(A2,5) ought to work to convert them to 5
ZIPs.
If they are numbers, then =LEFT(TEXT(A2,"000000000"),5) should do it (and on
a quick test it looks as if that would probably work with the text ones
too).
--
David Biddulph

"MichaelRobert" wrote in message
...
David:

Thanks for the suggestion but I don't think that 'stored as text' is the
problem. The FormatCellsSpecialZip Code command has no trouble adding
dropped leading zeroes. And it will not convert 5+4 ZIPs to 5 ZIPS after I
have formatted to 'number'.

Is there anyhting else?
M

"David Biddulph" wrote:

Your 5+4 ZIPs were presumably stored as text, rather than as numbers, so
formatting the cell has no effect.
--
David Biddulph

"MichaelRobert" wrote in
message
...
Every day I receive a list of Zip Codes covering Sales Leads. The
as-received
format includes regular 5 digit zips, 5+4 zips, and 5 digit zips with
the
leading zeros omitted, but I need to change it to 5 digit to process
further.

Routinely, I use the command "FormatCellsSpecialZip Code" to add the
leading zeros back. But the command seems unable to convert a '5+4'
format
to
a simple 5 digit format, and I end up having to delete the hypen and
the
last
4 digits manually. Am I missing a trick?

Mike

FormatCellsSpecialZip Code
 

David and Beege:

What I am taking from this discussion is that the FormatCellsSpecialZip
Code (5 digits) command will somehow change the format of 5 digits from ?? to
a 5 digit Zip Code (ie no change at all except to add any missing digits as
leading zeroes) . While the 5+4 command will change a 9 digit string into a
5-4 string.

Is that right? If so, it hardly seems worth having a special command.

Mike

"Beege" wrote:

ISTEXT is going return TRUE to the 5-4 zip code. If you enter
00000-1111, Excel assumes its a text entry, otherwise there'd be
confusion on whether it was a zip or a subtraction formula. Strings like
12345 are assumed to be numbers, unless you tell it otherwise, and you
can format numbers to have leading and trailing zeroes, but you can't
format text like that.

My wish is that people would treat zip codes, telephone numbers, serial
numbers (e.g part numbers) as they would text, because nobody is going
to do any calculations to those, except as a text character string.

Maybe clearer, maybe not. Hope it is.

Beege



MichaelRobert wrote:
Thanks, David.

ISTEXT returns FALSE; ISNUMBER returns TRUE.

The LEFT command works well - thank you.

Btw, even if I start with a fresh worksheet, and enter numbers to create a
5-4 ZIP (like 32034-1234) the FormatCellsSpecialZip Code command does not
convert the entry to 5 digits. Should it?

Mike

"David Biddulph" wrote:

What do =ISTEXT(A2) and =ISNUMBER(A2) say for your 5+4 ZIPs? I would still
suspect that you've got text rather than numbers.

If you do have text, then =LEFT(A2,5) ought to work to convert them to 5
ZIPs.
If they are numbers, then =LEFT(TEXT(A2,"000000000"),5) should do it (and on
a quick test it looks as if that would probably work with the text ones
too).
--
David Biddulph

"MichaelRobert" wrote in message
...
David:

Thanks for the suggestion but I don't think that 'stored as text' is the
problem. The FormatCellsSpecialZip Code command has no trouble adding
dropped leading zeroes. And it will not convert 5+4 ZIPs to 5 ZIPS after I
have formatted to 'number'.

Is there anyhting else?
M

"David Biddulph" wrote:

Your 5+4 ZIPs were presumably stored as text, rather than as numbers, so
formatting the cell has no effect.
--
David Biddulph

"MichaelRobert" wrote in
message
...
Every day I receive a list of Zip Codes covering Sales Leads. The
as-received
format includes regular 5 digit zips, 5+4 zips, and 5 digit zips with
the
leading zeros omitted, but I need to change it to 5 digit to process
further.

Routinely, I use the command "FormatCellsSpecialZip Code" to add the
leading zeros back. But the command seems unable to convert a '5+4'
format
to
a simple 5 digit format, and I end up having to delete the hypen and
the
last
4 digits manually. Am I missing a trick?

Mike