Rearranging Alphabetical List

[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

 

[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

 

[Gord Dibben]

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

Now sort on the Stae column


Gord Dibben MS Excel MVP

 

[FLKulchar]

Perfect...Thanks

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

 

[FLKulchar]

Thanks...perfect!

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

 

[FLKulchar]

Thanks...very good!

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

Now sort on the Stae column


Gord Dibben MS Excel MVP

 

[FLKulchar]

This formula gives me ONLY the states in column B.

What next?

 

[FLKulchar]

YES...you are correct...it will work!!

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