Sorting Google Sheets by Mailing Address

Question

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"     

Answer 1

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.

Answer 2

Short answer

Add column for the countries, then sort by that column.

Solution

Below are the instructions to extract the country from each address to an auxiliary column to make it possible to sort the address by country.

Assuming

1. the countries are known and wrote on a standard form
2. the data from companies is in columns A,B and C of Sheet1.
3. the number of companies is "small".

Try

1. Create a list of the countries, let say that the list is on Sheet2!A:A
2. Add the following formula to D1 on Sheet1

=ArrayFormula(
  offset(Sheet2!$A$1,
   value(
    trim(
     query(IF(iserror(SEARCH(Sheet2!$A$1:$A$2,B1)),,ROW(Sheet2!$A$1:$A$2)),,1000)
    ) 
   )-1,
   0)
 )

3. Fill down as necessary
4. Sort columns A-D from Sheet1 by column D

Note: If the above solution makes your spreadsheet become to slow to recalculate, an alternative is to use Google Apps Script.

How it works

• SEARCH is used in an array formula to search for each of the countries in the list of countries. The result will be an array of errors and a number. The position of the number corresponds to the row number the country found.
• IFERROR is used to replace the error elements by blanks.
• IF and ROW are used to replace the number returned by SEARCH by the row number. This row number indicates the row of the country.
• QUERY is used to concatenate the elements of the array. It uses a space as element separator
• TRIM removes the spaces. The result is a string.
• VALUE converts the string to a number.
• OFFSET is used to get the country from the list of countries.