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

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?
 
N

Norman Jones

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.
 
R

Ron Rosenfeld

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
 
M

Myrna Larson

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.
 

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

How to merge Excel address columns when some zips begin with zeros 4
Excel and zip codes 10
Adding a HYPHEN to a 9 digit zip code column 7
More zip + 4 problems 1
look up zip code 4
Converting 5 digit zips to zip +4 3
only 5 digit zip codes 1
lookup function Question 5

Top