Google-sheets – Sorting Google Sheets by Mailing Address

google sheetssorting

I have a spreadsheet in Google Sheets (and Excel) that contains companies from many countries. The address cell has the full address including the country.
So:

Company name | Full Address | phone number |

It is possible to sort the sheet in such a way that all companies from the same country are grouped together? I thought about using the phone number, but in each country there many area codes.

Examples of rows:

Brightcove          "290 Congress St4th Floor, 4th Floor  P: 617-395-5846
                    Boston, MA 02210
                    United States of America"   
                    "View on Floor Plan:
                    South Hall (Upper) - SU2724"        


BroadView Software  "110 Adelaide Street East             "P: 647-255-3500
                    3rd Floor                             F: 416-778-0648"
                    Toronto, ON M5C 1K9
                    Canada"
                    "View on Floor Plan:
                    North Hall - N4424"

Best Answer

Address formats vary so much by country that it will be nearly impossible to reliably parse out the country from a mailing address in order to sort by it.

You're certainly better off having a separate "country" column for each row, then sorting becomes academic.

Related Solutions

Google-sheets – How to make some data on a Google Sheets auto-sorting

You can use the sort() function for that, but you have to have your data in one place, and an automatically sorted copy of that data in another place.

For example, say I have Sheet1 with my data:

   | A    | B    | C
=====================
 1 | This | this | 2
 2 | Is   | is   | 1
 3 | Test | test | 3

Then in Sheet2, cell A1, I would put this function:

=sort(Sheet1!A:C, 3, TRUE)

This would show my data, but sorted by column C (the third column), ascending.

   | A    | B    | C
=====================
 1 | Is   | is   | 1
 2 | This | this | 2
 3 | Test | test | 3

Google-sheets – Sorting a protected range in Google spreadsheets

I am fighting with the same problem, I think it is because the protected cells are set statically and not affected by the sort. The reason is that Google Spreadsheets do not allow the selection of multiple ranges with are not adjacent to each other.

In other words, If you have a defined protected range in the Spreadsheet, it will always be the same rows and columns, regardless of your actions (adding rows and columns, sorting them and so on).

This is a huge drawback of the Google Spreadsheets.

Best Answer

Related Solutions

Google-sheets – How to make some data on a Google Sheets auto-sorting

Google-sheets – Sorting a protected range in Google spreadsheets

Related Topic