Coulmns with Zip Codes

[Vince48]

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

 

[CLR]

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

 

[Vince48]

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

 

[CLR]

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

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

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

 

[Debra Dalgleish]

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

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

:

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

 

[Vince48]

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

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

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

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

 

[Debra Dalgleish]

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

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

:

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

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

:


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

and 9-digit formats?

 

[Vince48]

Debra,

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

Thanks!

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

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

:

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

 

[Vince48]

Debra,

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

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

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

:

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

 

[Debra Dalgleish]

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")

Debra,

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

:

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

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

:



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

 

[CLR]

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

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

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


message news:[email protected]...

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

:

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

both
5-digit

and 9-digit formats?