Rearranging Alphabetical List

  • Thread starter F. Lawrence Kulchar
  • Start date
F

F. Lawrence Kulchar

I have several cities, in alphabetical order, listed in column A, such as:

A
Albany, NY
Anchoridge, AK
Atlanta, GA
Baltimore, MD
Baton Rouge, LA

etc.


Wichita, KS

QUESTION: hOW DO I REARRANGE THEM ALPHABETICAL, BY STATE???

(instead of 'by city')??

Thanks,

FLKulchar

Wich
 
T

Tom Hutchins

One way...

In column B (insert a new column B temporarily if needed), add a formula to
extract the 2-character state abbreviation:

=RIGHT(A1,2)

and copy down through all your rows of data (this example assumes your data
starts in A1). Select all the data and sort by column B.

Hope this helps,

Hutch
 
L

L. Howard Kittle

In the next column enter =RIGHT(A1,2) and pull down. Now sort both column
using the right column to sort on. Then delete helper column.

HTH
Regards,
Howard
 
G

Gord Dibben

Data>Text to Columns>Delimited by "Comma">Finish

Now sort on the Stae column


Gord Dibben MS Excel MVP
 
F

FLKulchar

Perfect...Thanks
Tom Hutchins said:
One way...

In column B (insert a new column B temporarily if needed), add a formula
to
extract the 2-character state abbreviation:

=RIGHT(A1,2)

and copy down through all your rows of data (this example assumes your
data
starts in A1). Select all the data and sort by column B.

Hope this helps,

Hutch
Click to expand...
 
F

FLKulchar

Thanks...perfect!
L. Howard Kittle said:
In the next column enter =RIGHT(A1,2) and pull down. Now sort both column
using the right column to sort on. Then delete helper column.

HTH
Regards,
Howard
Click to expand...
 
F

FLKulchar

YES...you are correct...it will work!!
Tom Hutchins said:
One way...

In column B (insert a new column B temporarily if needed), add a formula
to
extract the 2-character state abbreviation:

=RIGHT(A1,2)

and copy down through all your rows of data (this example assumes your
data
starts in A1). Select all the data and sort by column B.

Hope this helps,

Hutch
Click to expand...
 
Top