Sorting by count and state

[Kyle]

I have a large list of addresses, each address is really
in a different format. I need to be able to sort the list
by counties within states. Example of list:

12345 S. HArvey, Fort Hancock, TX 45678
PO box 14 Somewhere, MI 23454-345

I have a list of how zipcodes are done. The first 3
numbers indicate county. Does anyone have any
suggestions, even an answer that might take some time
would be helpful.

 

[Dave Peterson]

My suggestion is a very labor intensive.

Put each field into its own column. One row per record. Then you can sort it
nicely.

After you've gotten the zips in their own column, you can use a helper column to
get the first 3 characters.

Depending on what your data is (text or numbers), maybe something like:

=left(h1,3)
or
=left(text(h1,"00000"),3)

 

[Fred Smith]

If your cells *always* end with 2-character State, a space, and 5 or
8-character zip code, you can do it relatively easily with a couple of if
statements.

First, get the State and Zip based on the presence of a dash:
=IF(iserror(find("-",right(a1,5))),right(a1,8),left(right(a1,12),8))

Now, State is left-most 2 characters, zip is right-most 5.