Convert Full Country Names Into Country Codes

Carlito · June 3, 2019

MSExcel

This one is useful for data preparation before importing records to a standard data extension. For example you will filter N records by a column Country. If you build a set of filters and filtered data extensions from scratch - this is not an issue. Look up the Country column in your source data file and make sure your filter uses the same value in the criteria property. What if you want to reuse a set of existing filters, filter activities and make your life easier but your filters contain abbreviated country codes and the input file has full names in the Country column. Values don’t match but this is a simple formula for MS Excel file does the trick. You could do a conversion directly in the DE using the SQL CASE statement but in case you need to use an Excel file and import it, here is how.

Reference table for country names and codes

  1. In MS Excel create a new file
  2. Enter the following column heading names: Country, ISO-2, ISO-3, FIPS
  3. Fill out each column with relevant data for example: Austria, AT, AUT, AU
  4. Save As countryCodes.xlsx

Table 1 - countryCodes.xlsx file

Country ISO-2 ISO-3 FIPS
Austria AT AUT AU
Poland PL POL PL
United Kingdom GB GBR UK

Find and Replace Cells in the Source Input File

Table 2 - dataInput.xlsx file

A B C D
12345678 heidi@domain.at AT Austria
14101939 janusz@domain.pl PL Poland
87654321 sam@domain.uk GB United Kingdom

To change country names to country codes do the following:

  1. Open the file
  2. Insert a new column adjacent to the country column (column C in the example)
  3. In the C column enter the formula:
=vlookup(D1,countryCodes!A:D,2,FALSE)

Result: The formula retrieves a country code from the column_index 2 of the countryCodes.xlsx file, that is ISO-2. To retrieve ISO-3 value, use the 3 for column_index and 4 to get FIPS codes.

Resources

Twitter, Facebook