Coulmns with Zip Codes
 

How do I combine and sort a column of zip codes that has data in both 5-digit
and 9-digit formats?

You could use a helper column and make them all 9 diget.........

=IF(LEN(A1)=5,A1&"-"&"0000",A1) and copy down, then Copy PasteSpecial
Values on the whole column

Then after sorting if you wish you can remove the -0000 with Edit Replace
-0000 with nothing Replace all

Vaya con Dios,
Chuck, CABGx3



" wrote
in message ...
How do I combine and sort a column of zip codes that has data in both
5-digit
and 9-digit formats?

Chuck,

Thanks for your reply. Close, but no cigar yet! If you apply the logical test
[=if(LEN(A1)=5, A1 & "-" & "0000"...], the helper cloumn drops the initial
zero in zip codes starting with "0", such as those here in Massachusetts.
Even if you later try to change the format of the helper column to "Zip + 4",
you still can't get the initial zero to appear. When you then try to sort the
worksheet by that column, the initial zero Zips don't come up first.

Vince

" wrote:

How do I combine and sort a column of zip codes that has data in both 5-digit
and 9-digit formats?

I don't understand Vince..........I just tried it again with some leading
zero Zips and it worked fine on my Excel 2000..........what version are you
using?...............are you sure you Copy PasteSpecial Values to get
rid of the formulas before sorting the HKelper column?

Vaya con Dios,
Chuck, CABGx3


" wrote in
message ...
Chuck,

Thanks for your reply. Close, but no cigar yet! If you apply the logical
test
[=if(LEN(A1)=5, A1 & "-" & "0000"...], the helper cloumn drops the initial
zero in zip codes starting with "0", such as those here in Massachusetts.
Even if you later try to change the format of the helper column to "Zip +
4",
you still can't get the initial zero to appear. When you then try to sort
the
worksheet by that column, the initial zero Zips don't come up first.

Vince

" wrote:

How do I combine and sort a column of zip codes that has data in both
5-digit
and 9-digit formats?

You could use: =IF(LEN(A2)5,TEXT(A2,"00000-0000"),TEXT(A2,"00000"))

wrote:
Chuck,

Thanks for your reply. Close, but no cigar yet! If you apply the logical test
[=if(LEN(A1)=5, A1 & "-" & "0000"...], the helper cloumn drops the initial
zero in zip codes starting with "0", such as those here in Massachusetts.
Even if you later try to change the format of the helper column to "Zip + 4",
you still can't get the initial zero to appear. When you then try to sort the
worksheet by that column, the initial zero Zips don't come up first.

Vince

" wrote:


How do I combine and sort a column of zip codes that has data in both 5-digit
and 9-digit formats?



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Chuck,

I'm using Excel 2003. The problem appears to be that although the Zip Code
in theoretical column A appears as a 5-digit number in Cell A1, in the
formula bar it shows up as a just a four digit number, i.e. the Zip Code
02446 shows up as 2446 in the formula bar.

Debra,

Thanks for responding. I also tried your solution and excel told me I had an
error in the formula.

Thanks for your help.

Vince

"CLR" wrote:

I don't understand Vince..........I just tried it again with some leading
zero Zips and it worked fine on my Excel 2000..........what version are you
using?...............are you sure you Copy PasteSpecial Values to get
rid of the formulas before sorting the HKelper column?

Vaya con Dios,
Chuck, CABGx3


" wrote in
message ...
Chuck,

Thanks for your reply. Close, but no cigar yet! If you apply the logical
test
[=if(LEN(A1)=5, A1 & "-" & "0000"...], the helper cloumn drops the initial
zero in zip codes starting with "0", such as those here in Massachusetts.
Even if you later try to change the format of the helper column to "Zip +
4",
you still can't get the initial zero to appear. When you then try to sort
the
worksheet by that column, the initial zero Zips don't come up first.

Vince

" wrote:

How do I combine and sort a column of zip codes that has data in both
5-digit
and 9-digit formats?

Perhaps you missed a bracket when you copied and pasted the formula.

wrote:
Chuck,

I'm using Excel 2003. The problem appears to be that although the Zip Code
in theoretical column A appears as a 5-digit number in Cell A1, in the
formula bar it shows up as a just a four digit number, i.e. the Zip Code
02446 shows up as 2446 in the formula bar.

Debra,

Thanks for responding. I also tried your solution and excel told me I had an
error in the formula.

Thanks for your help.

Vince

"CLR" wrote:


I don't understand Vince..........I just tried it again with some leading
zero Zips and it worked fine on my Excel 2000..........what version are you
using?...............are you sure you Copy PasteSpecial Values to get
rid of the formulas before sorting the HKelper column?

Vaya con Dios,
Chuck, CABGx3


" wrote in
message ...

Chuck,

Thanks for your reply. Close, but no cigar yet! If you apply the logical

test

[=if(LEN(A1)=5, A1 & "-" & "0000"...], the helper cloumn drops the initial
zero in zip codes starting with "0", such as those here in Massachusetts.
Even if you later try to change the format of the helper column to "Zip +

4",

you still can't get the initial zero to appear. When you then try to sort

the

worksheet by that column, the initial zero Zips don't come up first.

Vince

" wrote:


How do I combine and sort a column of zip codes that has data in both

5-digit

and 9-digit formats?





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Debra,

That worked. I must have copied it wrong. When you sort the worksheet, you
have to select "Sort text and numbers separately"

Thanks!

"Debra Dalgleish" wrote:

You could use: =IF(LEN(A2)5,TEXT(A2,"00000-0000"),TEXT(A2,"00000"))

wrote:
Chuck,

Thanks for your reply. Close, but no cigar yet! If you apply the logical test
[=if(LEN(A1)=5, A1 & "-" & "0000"...], the helper cloumn drops the initial
zero in zip codes starting with "0", such as those here in Massachusetts.
Even if you later try to change the format of the helper column to "Zip + 4",
you still can't get the initial zero to appear. When you then try to sort the
worksheet by that column, the initial zero Zips don't come up first.

Vince

" wrote:


How do I combine and sort a column of zip codes that has data in both 5-digit
and 9-digit formats?



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Debra,

One more thing: can you explain the logic of the solution?

"Debra Dalgleish" wrote:

You could use: =IF(LEN(A2)5,TEXT(A2,"00000-0000"),TEXT(A2,"00000"))

wrote:
Chuck,

Thanks for your reply. Close, but no cigar yet! If you apply the logical test
[=if(LEN(A1)=5, A1 & "-" & "0000"...], the helper cloumn drops the initial
zero in zip codes starting with "0", such as those here in Massachusetts.
Even if you later try to change the format of the helper column to "Zip + 4",
you still can't get the initial zero to appear. When you then try to sort the
worksheet by that column, the initial zero Zips don't come up first.

Vince

" wrote:


How do I combine and sort a column of zip codes that has data in both 5-digit
and 9-digit formats?



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

I assumed a zip code would have between 3 and 5 characters in the front
portion.
If there are more than 5 characters, I assume it's a zip + 4, and the
last 4 characters should follow the hyphen.

The formula check the length of the number: LEN(A2)

If the length is greater than 5, it formats it with 5 characters and
zero placeholders, a hyphen, and 4 characters: TEXT(A2,"00000-0000")

If the length is less than or equal to 5 characters, it formats it with
5 characters and zero placeholders: TEXT(A2,"00000")

wrote:
Debra,

One more thing: can you explain the logic of the solution?

"Debra Dalgleish" wrote:


You could use: =IF(LEN(A2)5,TEXT(A2,"00000-0000"),TEXT(A2,"00000"))

wrote:

Chuck,

Thanks for your reply. Close, but no cigar yet! If you apply the logical test
[=if(LEN(A1)=5, A1 & "-" & "0000"...], the helper cloumn drops the initial
zero in zip codes starting with "0", such as those here in Massachusetts.
Even if you later try to change the format of the helper column to "Zip + 4",
you still can't get the initial zero to appear. When you then try to sort the
worksheet by that column, the initial zero Zips don't come up first.

Vince

" wrote:



How do I combine and sort a column of zip codes that has data in both 5-digit
and 9-digit formats?


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Hi Vince..........

My formula works fine when the Zip in the cell is actually 01234, but does
not work when the number is actually 1234 and the cell is formatted as
"Custom 00000" to give the appearance of a 5-diget number, which I suspect
is the case with your data since only 1234 shows in the formula bar
...........it matters not tho, if you have a working solution already, so be
it..........

Vaya con Dios,
Chuck, CABGx3


" wrote in
message ...
Chuck,

I'm using Excel 2003. The problem appears to be that although the Zip Code
in theoretical column A appears as a 5-digit number in Cell A1, in the
formula bar it shows up as a just a four digit number, i.e. the Zip Code
02446 shows up as 2446 in the formula bar.

Debra,

Thanks for responding. I also tried your solution and excel told me I had
an
error in the formula.

Thanks for your help.

Vince

"CLR" wrote:

I don't understand Vince..........I just tried it again with some
leading
zero Zips and it worked fine on my Excel 2000..........what version are
you
using?...............are you sure you Copy PasteSpecial Values to
get
rid of the formulas before sorting the HKelper column?

Vaya con Dios,
Chuck, CABGx3


"
wrote in
message ...
Chuck,

Thanks for your reply. Close, but no cigar yet! If you apply the
logical
test
[=if(LEN(A1)=5, A1 & "-" & "0000"...], the helper cloumn drops the
initial
zero in zip codes starting with "0", such as those here in
Massachusetts.
Even if you later try to change the format of the helper column to
"Zip +
4",
you still can't get the initial zero to appear. When you then try to
sort
the
worksheet by that column, the initial zero Zips don't come up first.

Vince

" wrote:

How do I combine and sort a column of zip codes that has data in
both
5-digit
and 9-digit formats?