zip codes

[Guest]

Dear Readers
I have a list of 2000 names with 5 digit and 9 digit zip codes. They will not sort together, the 5 digits are before the 9 digits. the 9 digits are separated with an -example 42000-1000. the 5 digits have 42000. How can I sort all together so the lowest number whether a 5 digit or a 9 digit is sorted numerically or convert the 5 digits to a 9 digit with -ooo0 after the 5 digits already there. Your help will be very valuable to me. Thank yo
Rich

 

[Dave R.]

The problem I suggest is that excel sees 42000 as a number, and 42000-1 as
text, so it treats them diferently.

Try formatting them ALL as TEXT, then sort. It should sort correctly.

Dear Readers,
I have a list of 2000 names with 5 digit and 9 digit zip codes. They will

not sort together, the 5 digits are before the 9 digits. the 9 digits are
separated with an -example 42000-1000. the 5 digits have 42000. How can I
sort all together so the lowest number whether a 5 digit or a 9 digit is
sorted numerically or convert the 5 digits to a 9 digit with -ooo0 after the
5 digits already there. Your help will be very valuable to me. Thank you

 

[DDM]

Rich, here's one way:

Assuming your zip codes are in Column F, starting with Row 2, you can insert
a helper column to the right. To the right of the first zip code, enter this
formula =LEFT(F2,5). Copy it down the column, then sort on that column.

DDM
"DDM's Microsoft Office Tips and Tricks"
Visit us at www.ddmcomputing.com

Dear Readers,
I have a list of 2000 names with 5 digit and 9 digit zip codes. They will

not sort together, the 5 digits are before the 9 digits. the 9 digits are
separated with an -example 42000-1000. the 5 digits have 42000. How can I
sort all together so the lowest number whether a 5 digit or a 9 digit is
sorted numerically or convert the 5 digits to a 9 digit with -ooo0 after the
5 digits already there. Your help will be very valuable to me. Thank you

 

[David McRitchie]

Hi Rich,
You can fix your US zip codes in place with a macro
rather than an extra helper column to convert
them to text and not remove the leading zero.
Fix up for 5 digit US zip codes (#fixUSzip5)
http://www.mvps.org/dmcritchie/excel/join.htm#fixUSzip5

select the column and run the macro.

Help with macros if your not familiar with installing and using
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Dear Readers,
I have a list of 2000 names with 5 digit and 9 digit zip codes. They will not sort together, the 5 digits are before the 9

digits. the 9 digits are separated with an -example 42000-1000. the 5 digits have 42000. How can I sort all together so the
lowest number whether a 5 digit or a 9 digit is sorted numerically or convert the 5 digits to a 9 digit with -ooo0 after the 5
digits already there. Your help will be very valuable to me. Thank you