When exporting country data from your Facebook ads account, you don’t get simple country names in your reports. Meta uses the ISO 3166-1 alpha 2 codes (I know, catchy). For example ‘GB’ for the United Kingdom. And if you want clear and accurate data to analyse, this causes a bit of a nightmare, or in my case, a literal headache.
But after a few hours of googling and Excel formulas later, I found a fix. So, I’m here to save you that precious time with a simple guide on how to quickly convert country codes to country names in excel and google sheets…
In this post
The Issue
We are currently running Facebook and Instagram lead ads for a client in well over a hundred different countries. I needed a report that showed exactly how many leads were coming from each country and the spend per country. But upon exporting this file from Facebook, instead of country names I was seeing country codes.
Some of the country codes were obvious and some weren’t. This data was going to inform some important decisions around optimisation in the account so they had to be correct. Plus, it was important that the report was as user-friendly as possible to share with the client.
The thought of having to manually change every country code to a country name filled me with dread. BUT – after some research and trial and error, I found a way to do it in just a few steps.
How to Convert Country Codes to Country Names
Here are the step-by-step instructions on converting country codes to country names, using a simple VLOOKUP formula. The below instructions can be followed if you’re using either Microsoft Excel or Google Sheets. In my case, I used Google Sheets.
Step 1 – Export the Report from Facebook
Once you’ve set up your report in Facebook with all the metrics you need and split by country, export the report. Then open it up in either Microsoft Excel or Google Sheets.
Step 2 – Create a New Tab with Country Names
You’ll need to create a source of the countries for the VLOOKUP formula to refer to. So, create a second tab in your spreadsheet and paste in the below (make sure to keep the country code in the first column and the country name in the second column):
List of country codes and country names👇
| AF | Afghanistan |
| AL | Albania |
| DZ | Algeria |
| AS | American Samoa |
| AD | Andorra |
| AO | Angola |
| AI | Anguilla |
| AQ | Antarctica |
| AG | Antigua and Barbuda |
| AR | Argentina |
| AM | Armenia |
| AW | Aruba |
| AU | Australia |
| AT | Austria |
| AZ | Azerbaijan |
| BS | Bahamas |
| BH | Bahrain |
| BD | Bangladesh |
| BB | Barbados |
| BY | Belarus |
| BE | Belgium |
| BZ | Belize |
| BJ | Benin |
| BM | Bermuda |
| BT | Bhutan |
| BO | Bolivia, Plurinational State of |
| BO | Bolivia |
| BA | Bosnia and Herzegovina |
| BW | Botswana |
| BV | Bouvet Island |
| BR | Brazil |
| IO | British Indian Ocean Territory |
| BN | Brunei Darussalam |
| BN | Brunei |
| BG | Bulgaria |
| BF | Burkina Faso |
| BI | Burundi |
| KH | Cambodia |
| CM | Cameroon |
| CA | Canada |
| CV | Cape Verde |
| KY | Cayman Islands |
| CF | Central African Republic |
| TD | Chad |
| CL | Chile |
| CN | China |
| CX | Christmas Island |
| CC | Cocos (Keeling) Islands |
| CO | Colombia |
| KM | Comoros |
| CG | Congo |
| CD | Congo, the Democratic Republic of the |
| CK | Cook Islands |
| CR | Costa Rica |
| CI | Côte d’Ivoire |
| CI | Ivory Coast |
| HR | Croatia |
| CU | Cuba |
| CY | Cyprus |
| CZ | Czech Republic |
| DK | Denmark |
| DJ | Djibouti |
| DM | Dominica |
| DO | Dominican Republic |
| EC | Ecuador |
| EG | Egypt |
| SV | El Salvador |
| GQ | Equatorial Guinea |
| ER | Eritrea |
| EE | Estonia |
| ET | Ethiopia |
| FK | Falkland Islands (Malvinas) |
| FO | Faroe Islands |
| FJ | Fiji |
| FI | Finland |
| FR | France |
| GF | French Guiana |
| PF | French Polynesia |
| TF | French Southern Territories |
| GA | Gabon |
| GM | Gambia |
| GE | Georgia |
| DE | Germany |
| GH | Ghana |
| GI | Gibraltar |
| GR | Greece |
| GL | Greenland |
| GD | Grenada |
| GP | Guadeloupe |
| GU | Guam |
| GT | Guatemala |
| GG | Guernsey |
| GN | Guinea |
| GW | Guinea-Bissau |
| GY | Guyana |
| HT | Haiti |
| HM | Heard Island and McDonald Islands |
| VA | Holy See (Vatican City State) |
| HN | Honduras |
| HK | Hong Kong |
| HU | Hungary |
| IS | Iceland |
| IN | India |
| ID | Indonesia |
| IR | Iran, Islamic Republic of |
| IQ | Iraq |
| IE | Ireland |
| IM | Isle of Man |
| IL | Israel |
| IT | Italy |
| JM | Jamaica |
| JP | Japan |
| JE | Jersey |
| JO | Jordan |
| KZ | Kazakhstan |
| KE | Kenya |
| KI | Kiribati |
| KP | Korea, Democratic People’s Republic of |
| KR | Korea, Republic of |
| KR | South Korea |
| KW | Kuwait |
| KG | Kyrgyzstan |
| LA | Lao People’s Democratic Republic |
| LV | Latvia |
| LB | Lebanon |
| LS | Lesotho |
| LR | Liberia |
| LY | Libyan Arab Jamahiriya |
| LY | Libya |
| LI | Liechtenstein |
| LT | Lithuania |
| LU | Luxembourg |
| MO | Macao |
| MK | Macedonia, the former Yugoslav Republic of |
| MG | Madagascar |
| MW | Malawi |
| MY | Malaysia |
| MV | Maldives |
| ML | Mali |
| MT | Malta |
| MH | Marshall Islands |
| MQ | Martinique |
| MR | Mauritania |
| MU | Mauritius |
| YT | Mayotte |
| MX | Mexico |
| FM | Micronesia, Federated States of |
| MD | Moldova, Republic of |
| MC | Monaco |
| MN | Mongolia |
| ME | Montenegro |
| MS | Montserrat |
| MA | Morocco |
| MZ | Mozambique |
| MM | Myanmar |
| MM | Burma |
| NA | Namibia |
| NR | Nauru |
| NP | Nepal |
| NL | Netherlands |
| AN | Netherlands Antilles |
| NC | New Caledonia |
| NZ | New Zealand |
| NI | Nicaragua |
| NE | Niger |
| NG | Nigeria |
| NU | Niue |
| NF | Norfolk Island |
| MP | Northern Mariana Islands |
| NO | Norway |
| OM | Oman |
| PK | Pakistan |
| PW | Palau |
| PS | Palestinian Territory, Occupied |
| PA | Panama |
| PG | Papua New Guinea |
| PY | Paraguay |
| PE | Peru |
| PH | Philippines |
| PN | Pitcairn |
| PL | Poland |
| PT | Portugal |
| PR | Puerto Rico |
| QA | Qatar |
| RE | Réunion |
| RO | Romania |
| RU | Russian Federation |
| RU | Russia |
| RW | Rwanda |
| SH | Saint Helena, Ascension and Tristan da Cunha |
| KN | Saint Kitts and Nevis |
| LC | Saint Lucia |
| PM | Saint Pierre and Miquelon |
| VC | Saint Vincent and the Grenadines |
| WS | Samoa |
| SM | San Marino |
| ST | Sao Tome and Principe |
| SA | Saudi Arabia |
| SN | Senegal |
| RS | Serbia |
| SC | Seychelles |
| SL | Sierra Leone |
| SG | Singapore |
| SK | Slovakia |
| SI | Slovenia |
| SB | Solomon Islands |
| SO | Somalia |
| ZA | South Africa |
| GS | South Georgia and the South Sandwich Islands |
| SS | South Sudan |
| ES | Spain |
| LK | Sri Lanka |
| SD | Sudan |
| SR | Suriname |
| SJ | Svalbard and Jan Mayen |
| SZ | Swaziland |
| SE | Sweden |
| CH | Switzerland |
| SY | Syrian Arab Republic |
| TW | Taiwan, Province of China |
| TW | Taiwan |
| TJ | Tajikistan |
| TZ | Tanzania, United Republic of |
| TH | Thailand |
| TL | Timor-Leste |
| TG | Togo |
| TK | Tokelau |
| TO | Tonga |
| TT | Trinidad and Tobago |
| TN | Tunisia |
| TR | Turkey |
| TM | Turkmenistan |
| TC | Turks and Caicos Islands |
| TV | Tuvalu |
| UG | Uganda |
| UA | Ukraine |
| AE | United Arab Emirates |
| GB | United Kingdom |
| US | United States |
| UM | United States Minor Outlying Islands |
| UY | Uruguay |
| UZ | Uzbekistan |
| VU | Vanuatu |
| VE | Venezuela, Bolivarian Republic of |
| VE | Venezuela |
| VN | Viet Nam |
| VN | Vietnam |
| VG | Virgin Islands, British |
| VI | Virgin Islands, U.S. |
| WF | Wallis and Futuna |
| EH | Western Sahara |
| YE | Yemen |
| ZM | Zambia |
| ZW | Zimbabwe |
Step 3 – Use VLOOKUP to Convert the Country Codes
Head back to the first tab of your spreadsheet and insert a new column for your country names. In the first empty cell in this column, start with the below formula:
=VLOOKUP(search_key, range, index, [is_sorted])
You’ll then need to amend this formula based on your spreadsheet layout. I’ve explained each element below:
- Search_Key – In place of this, enter in the cell number of the country code you want to convert. For example, looking at our sheet below, I entered in B4.
- Range – In place of this, enter in the name of your second tab (where you entered in the source data) and the range of columns that should be searched to find the country name. For example, if you look at our sheet below, I entered Sheet1!A:B.
Index – In place of this, you’ll need the exact column number within that range that includes the country name. For us, this is ‘2’ because Column B is the second column in the range.
Is Sorted – This can be used if the data is sorted in some way. Ours isn’t, so here we entered ‘false’.
Once you’ve entered in all those elements, your formula should look something like this:
=VLOOKUP(B4, Sheet1!A:B, 2, False)
Click enter, and voila! That empty cell should now show the correct country name.
Step 4 – Apply to All Cells
Once you’re happy that the formula in the first cell worked, Google Sheets or Microsoft Excel may suggest implementing this formula to the rest of the cells below it – in which case you can click yes.
Otherwise you can click on the bottom right hand corner of the cell and drag it across to all of the empty cells below to replicate.
And You’re Done!
I hope this guide saved you that all-important time of googling and testing formulas to convert country codes to country names.
Now you can easily analyse the country data from your Facebook ads and share this in accessible reports.
