Excel Text Sorting Help

  • Thread starter Thread starter John
  • Start date Start date
J

John

I have a great deal of inforamtion I'm trying to sort.
The info includes addresses that are not all in the same
format. I need to beable to pull the state from the
address contained in one cell,and then sort by state.
Basically all I need is a way to sesarch a cell for a
state abbreviation and then return it to another cell.
Any insight or solutions you might have would be
aprpeciated. Thanks.
 
Maybe you can show (not attach a file) how the info look, is the state abbr.
after a comma sign it should fairly pretty easy using the mid function, so
post back with a
few example of the addresses. If not I would assume that the address would
look like this

8500 Blarney Stone Ave Springfield, VA 22152

assuming the above resides in A1 VA can be retrieved with

=LEFT(TRIM(MID(A1,FIND(",",A1)+1,255)),2)

if that is of no help post back
 
That worked a little better than what I had thanks. But
there are still some problems. Some people have multiple
commas in there address so it makes the return something
other than a state. (here is a quick sample)

name number po box 12345 somewhere, UT 43256 us

name number 4323 S. Here St., Overhere, MI 32423-4356 us

The inconsistencies make it difficult i know. If you have
any other ideas i would appreciate it. Thanks for your
help so far.
 
John,

try this

=LEFT(TRIM(RIGHT(A1,LEN(A1)-SEARCH("^^",SUBSTITUTE(A1,",","^^",LEN(A1)-LEN(S
UBSTITUTE(A1,",","")))))),2)
 
That worked great, Thanks for all your help.
-----Original Message-----
John,

try this

=LEFT(TRIM(RIGHT(A1,LEN(A1)-SEARCH("^^",SUBSTITUTE (A1,",","^^",LEN(A1)-LEN(S
UBSTITUTE(A1,",","")))))),2)

--

Regards,

Peo Sjoblom




.
Click to expand...
 
Back
Top