Help with Excell & Zip codes

[BChellberg]

I am having a problem getting Excell to sort mixed 5 digit and 9 digit. I want it to sort first by the 5 digits and then by the 9. Can this be done?
Thanks for your time,
Bill
w.s.c@sbcglobal.net

 

[WalterContrata]

What format are the Zip Codes in? If you have them as text, e.g.

'06339
'063390111
'063390222
'91361
'913610111
'913610222

with an apostrophe out front, I think that Excel will sort them as you wish.

Best Regards,
Walter

 

[macropod]

Hi Bill,

If the zip codes are simply stored as numeric strings (ie numbers), you'll need a 'helper' column to use as the primary sort key, filled with a formula like:
=LEN(A1)>5
where 'A1' is the cell with the zip code.

Cheers

 

[SkipVought]

Bill,

You need to understand the huge difference between NUMBERS and DIGIT STRINGS.

You know how numbers sort, but digit strings much differently.

[tt]
1
10
100
2
20
200
3
30
300
[/tt]

To be able to sort 5-digit and 9-digit zip codes, they must be converted to STRINGS. each 5-digit can be converted by a preceeding tic as Walter has suggested.

The 9-digit zips are already strings if they are entered as
[tt]
99999-9999
[/tt]


Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[BChellberg]

Thanks to all of you --- Walter, macropod, and Skip! Why did I not think of changing them to a alpha string!? Also the idea of using a helper column is a good option, however, it will only sort by the first 5 digits, which may be OK.
I appreciate the help,
Bill

 

[KenWright]

Why will it only sort by the first 5. Macropods answer should do it for you. Use the helper as the primary sort key, and your numbers as the second. This will group them all into 5 and 9 and sort within each.

Regards
Ken..........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[BChellberg]

Hi Ken,
I was thinking the ">5" in his formula would cause it to sort out the 5 and 9 digit codes rather than intermingle them, which is what I want.
Thanks,
Bill

 

[macropod]

Hi Bill,

You're right about my formula causing the 5 and 9 digit codes to be sorted separately.

If you've got numbers that you need an intermingled sort for, you could again use a 'helper' column with a formula like:
=TEXT(A1,IF(LEN(A1)<6,"00000","000000000"))
instead of having to convert the existing numbers to text strings. This time, though, you'd only sort by the 'helper' column.

Cheers

 

[WalterContrata]

macropod,

Neat!

 

[BChellberg]

Macropod: Thanks for that last formula, Macropod. (Sorry, your handle makes me think I'm talking to a dinosaur!)
Is your formula "=TEXT(A1,IF(LEN(A1)<6,"00000","000000000"))" to convert the numeric strings to text strings? then sort by the text strings? Am I getting the concept right?

thanks,
Bill

 

[macropod]

If I was a dinosaur, my name would probably end in 'don' or 'saur' as in Macrodon or Macrosaur ...

Anyway, getting back to more mundane issues, yes, the formula converts a numeric string to a text string, and you ten sort by the text string.

Cheers
PS: The term 'macropod' typically refers to a large marsupial from a land down-under.

 

[KenWright]

Just curious now because I don't know how the US zip codes work, but if you have 5 digit ones and 9 digit ones, eg as per the previous example given:-

'06339
'063390111
'063390222
'91361
'913610111
'913610222

Should the 5 digit ones need to be appended with 0s to make them 9, or prefixed with 0s to make them 9.

Cheers
Ken.........


----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[macropod]

Hi Ken,

US zip codes take two forms:
5 digit, represented as "00000"
5+4 (9) digit, represented as "00000-0000".

Cheers

 

[KenWright]

So the 5 digit number is the same level as the first 5 digits of the 9 digit number, and the additional 4 is just a lower level of the 5 digit number.

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[BChellberg]

Thanks again, Macropod. I will try to put this all together now that I "think" I understand.

You are correct Ken, the last 4 digits are a lower level of sort and relatively useless unless a mailing has all 9 digit codes. With 9 digit codes the mail fee is less.

Bill