0

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"     
  • Welcome to Web Applications. Please take the tour and checkout How to Ask. By the way, how do you have a spreadsheet in Google Sheets and Excel at the same time? – Rubén Sep 15 '16 at 1:23
  • Can you add a couple lines of the spreadsheet so we can see the format of the address and phone number fields? – ode2k Sep 15 '16 at 1:40
  • My apologies, cant seem to format it properly. – Alex Sep 15 '16 at 2:06
  • I've attempted to fix the formatting. It looks like you have more columns in there than you are asking about. You might want to remove that information so it doesn't clutter things up. – ale Sep 15 '16 at 12:25
  • Microsoft Excel, unless you're asking about the web interface of Microsoft Excel Online (part of Office 365) is off-topic here. – ale Sep 15 '16 at 12:25
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.

0

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)
 )
  1. Fill down as necessary
  2. 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.