Digital Marketing, Marketing Cloud, AMPScript, HTML/CSS/JS, SQL, XLSX.

XLSX - Convert Full Country Names Into Codes

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.

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 Source Input File

Table 2 - dataInput.xlsx file

A

B

C

D

12345678

[email protected]

AT

Austria

14101939

[email protected]

PL

Poland

87654321

[email protected]

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

This blog post is actually just a Google Doc! Create your own blog with Google Docs, in less than a minute.