How do I correctly sort a list of zip codes that has both 5-digit.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How do I correctly sort a list of zip codes that has both 5-digit zip codes
and nine-digit zip+4 codes?
 
Hi Vince,

Assume the zip codes populate cells A1:A100.
Use a helper column, say column B. In B1 enter the formula
=len(A1)
and copy down to B100. Then sort A1:B100 by Column B and then Column A.
 
How do I correctly sort a list of zip codes that has both 5-digit zip codes
and nine-digit zip+4 codes?
Click to expand...

How are the codes stored? Are they numbers with a custom formatting; text
strings; or some combination?


--ron
 
I would use a helper column with a formula like this

=IF(ISNUMBER(A1),TEXT(A1,"00000"),A1)

Then sort on this column. That will generate this order

10021
10021-0123
10022
10022-0123

etc.
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

mixing zip with zip+4 and then sorting 2
How do I zip sort mailing list when I have 5 digit AND 9 digit zip 1
How to merge Excel address columns when some zips begin with zeros 4
Zip code filtering 1
Excel and zip codes 10
lookup function Question 5
Adding a HYPHEN to a 9 digit zip code column 7
look up zip code 4

Back
Top