Custom Sorting with commas for City and States

I want to sort all of the "column2" by alphabetical order. The order will be first by state and then by city. For example the first box the column2 order should be (Fountain Hills, AZ) properties first followed by (Mesa, AZ) properties second followed by (Peoria, AZ) etc.... ending with (Flower Mound, TX) last.

The issue that I'm having is that when I create a custom sort the comma after the city indicates a new value being AZ. For example my custom sorting looks like this: Fountain Hills, AZ, Mesa, AZ, Peoria, AZ. This is limiting my ability to sort the files as is with city, state. I solved this issue by removing the commas after all the cities. This worked but was time consuming. Is there a faster way? The workbook is below. Thanks fellower excelers!


Development Projects Mr Excel.xlsx

I think I would choose another way to solve a problem like this. If this is better than your present method I don't know.

What I would do would first of all to convert the tables to "normal" ranges. I would then insert a helper column between column2 and column3.

In the helper column cell D8 I would put a formula "=Right(C8,2)" so if C8 value was "Flower Mound, TX" the value in the helper column (D8) would be "TX"
After copying this formula down to D36 I would then go to "Data" tag and select "Sort" and set up to sorting rules i.e. 1st priority sort on values range "D9:D36" A->Z and 2nd priority sort range "C9:C36" A->Z. (Selecting range "B8:F36" and clicking "Sort" will show the setup of rules)

Having sorted all the different ranges I would then delete the helper column.

Alf