Last Updated on September 12, 2023 by “Talha Saqib”
You would also have faced the same problem time and time again. You are given an Excel or CSV file with tons of data and are asked to import all of the data to Salesforce org. You check the data. The file has no column for country names but includes country codes that make sense. The records are less than 50,000 so it makes sense that you use Salesforce’s standard Data Import Wizard. You select the desired object, choose the needed settings, and start mapping your data. You map all of the fields but when it comes time to map the CountryCode field, you realize that Data Import Wizard doesn’t give the option to map the CountryCode field of the standard object and there goes all your joy. Instantly, you search the internet to convert Country Code to Country Name.
Yes, I too am guilty of forgetting that the data file needs to have Country Names instead of Country Codes as Salesforce Import Wizard cannot map country codes with data in the CSV. However, today, I am going to make things easy for myself and you guys. I am going to write about a common workaround to quickly convert Alpha 2 codes of countries into their full names directly inside a spreadsheet that can later be converted to desired file type and be used to import and map the countries correctly in Salesforce.
Problem
Need to convert Country Codes in an Excel/CSV to their respective Country Names.
Solution
There are multiple steps to solve this issue.
1. Use a Spreadsheet Tool
The first step is to have your data in a spreadsheet tool like Google Sheets, Microsoft Excel, or others. The tool should support the use of the VLOOKUP function. I will be referring to Google Sheets.
2. Country Code – Country Mapping
Now create a new sheet inside the Google sheets that should include two columns inside it. One should include all the country codes, Alpha 2, Alpha 3, or UN depending on your original data. The other column should include the respective country names. You can copy and paste the following data directly to your new Google sheet.
But make sure that out of two columns, the left one must be Country Code and the right one should be Country Names. This is because VLOOKUP always compares the first column mentioned in the range.
The mapping should be like this:
Dataset
Country | Alpha 2 | Alpha 3 |
Afghanistan | AF | AFG |
Albania | AL | ALB |
Algeria | DZ | DZA |
American Samoa | AS | ASM |
Andorra | AD | AND |
Angola | AO | AGO |
Anguilla | AI | AIA |
Antarctica | AQ | ATA |
Antigua and Barbuda | AG | ATG |
Argentina | AR | ARG |
Armenia | AM | ARM |
Aruba | AW | ABW |
Australia | AU | AUS |
Austria | AT | AUT |
Azerbaijan | AZ | AZE |
Bahamas | BS | BHS |
Bahrain | BH | BHR |
Bangladesh | BD | BGD |
Barbados | BB | BRB |
Belarus | BY | BLR |
Belgium | BE | BEL |
Belize | BZ | BLZ |
Benin | BJ | BEN |
Bermuda | BM | BMU |
Bhutan | BT | BTN |
Bolivia, Plurinational State of | BO | BOL |
Bolivia | BO | BOL |
Bosnia and Herzegovina | BA | BIH |
Botswana | BW | BWA |
Bouvet Island | BV | BVT |
Brazil | BR | BRA |
British Indian Ocean Territory | IO | IOT |
Brunei Darussalam | BN | BRN |
Brunei | BN | BRN |
Bulgaria | BG | BGR |
Burkina Faso | BF | BFA |
Burundi | BI | BDI |
Cambodia | KH | KHM |
Cameroon | CM | CMR |
Canada | CA | CAN |
Cape Verde | CV | CPV |
Cayman Islands | KY | CYM |
Central African Republic | CF | CAF |
Chad | TD | TCD |
Chile | CL | CHL |
China | CN | CHN |
Christmas Island | CX | CXR |
Cocos (Keeling) Islands | CC | CCK |
Colombia | CO | COL |
Comoros | KM | COM |
Congo | CG | COG |
Congo, the Democratic Republic of the | CD | COD |
Cook Islands | CK | COK |
Costa Rica | CR | CRI |
Côte d’Ivoire | CI | CIV |
Ivory Coast | CI | CIV |
Croatia | HR | HRV |
Cuba | CU | CUB |
Cyprus | CY | CYP |
Czech Republic | CZ | CZE |
Denmark | DK | DNK |
Djibouti | DJ | DJI |
Dominica | DM | DMA |
Dominican Republic | DO | DOM |
Ecuador | EC | ECU |
Egypt | EG | EGY |
El Salvador | SV | SLV |
Equatorial Guinea | GQ | GNQ |
Eritrea | ER | ERI |
Estonia | EE | EST |
Ethiopia | ET | ETH |
Falkland Islands (Malvinas) | FK | FLK |
Faroe Islands | FO | FRO |
Fiji | FJ | FJI |
Finland | FI | FIN |
France | FR | FRA |
French Guiana | GF | GUF |
French Polynesia | PF | PYF |
French Southern Territories | TF | ATF |
Gabon | GA | GAB |
Gambia | GM | GMB |
Georgia | GE | GEO |
Germany | DE | DEU |
Ghana | GH | GHA |
Gibraltar | GI | GIB |
Greece | GR | GRC |
Greenland | GL | GRL |
Grenada | GD | GRD |
Guadeloupe | GP | GLP |
Guam | GU | GUM |
Guatemala | GT | GTM |
Guernsey | GG | GGY |
Guinea | GN | GIN |
Guinea-Bissau | GW | GNB |
Guyana | GY | GUY |
Haiti | HT | HTI |
Heard Island and McDonald Islands | HM | HMD |
Holy See (Vatican City State) | VA | VAT |
Honduras | HN | HND |
Hong Kong | HK | HKG |
Hungary | HU | HUN |
Iceland | IS | ISL |
India | IN | IND |
Indonesia | ID | IDN |
Iran, Islamic Republic of | IR | IRN |
Iraq | IQ | IRQ |
Ireland | IE | IRL |
Isle of Man | IM | IMN |
Israel | IL | ISR |
Italy | IT | ITA |
Jamaica | JM | JAM |
Japan | JP | JPN |
Jersey | JE | JEY |
Jordan | JO | JOR |
Kazakhstan | KZ | KAZ |
Kenya | KE | KEN |
Kiribati | KI | KIR |
Korea, Democratic People’s Republic of | KP | PRK |
Korea, Republic of | KR | KOR |
South Korea | KR | KOR |
Kuwait | KW | KWT |
Kyrgyzstan | KG | KGZ |
Lao People’s Democratic Republic | LA | LAO |
Latvia | LV | LVA |
Lebanon | LB | LBN |
Lesotho | LS | LSO |
Liberia | LR | LBR |
Libyan Arab Jamahiriya | LY | LBY |
Libya | LY | LBY |
Liechtenstein | LI | LIE |
Lithuania | LT | LTU |
Luxembourg | LU | LUX |
Macao | MO | MAC |
Macedonia, the former Yugoslav Republic of | MK | MKD |
Madagascar | MG | MDG |
Malawi | MW | MWI |
Malaysia | MY | MYS |
Maldives | MV | MDV |
Mali | ML | MLI |
Malta | MT | MLT |
Marshall Islands | MH | MHL |
Martinique | MQ | MTQ |
Mauritania | MR | MRT |
Mauritius | MU | MUS |
Mayotte | YT | MYT |
Mexico | MX | MEX |
Micronesia, Federated States of | FM | FSM |
Moldova, Republic of | MD | MDA |
Monaco | MC | MCO |
Mongolia | MN | MNG |
Montenegro | ME | MNE |
Montserrat | MS | MSR |
Morocco | MA | MAR |
Mozambique | MZ | MOZ |
Myanmar | MM | MMR |
Burma | MM | MMR |
Namibia | NA | NAM |
Nauru | NR | NRU |
Nepal | NP | NPL |
Netherlands | NL | NLD |
Netherlands Antilles | AN | ANT |
New Caledonia | NC | NCL |
New Zealand | NZ | NZL |
Nicaragua | NI | NIC |
Niger | NE | NER |
Nigeria | NG | NGA |
Niue | NU | NIU |
Norfolk Island | NF | NFK |
Northern Mariana Islands | MP | MNP |
Norway | NO | NOR |
Oman | OM | OMN |
Pakistan | PK | PAK |
Palau | PW | PLW |
Palestinian Territory, Occupied | PS | PSE |
Panama | PA | PAN |
Papua New Guinea | PG | PNG |
Paraguay | PY | PRY |
Peru | PE | PER |
Philippines | PH | PHL |
Pitcairn | PN | PCN |
Poland | PL | POL |
Portugal | PT | PRT |
Puerto Rico | PR | PRI |
Qatar | QA | QAT |
Réunion | RE | REU |
Romania | RO | ROU |
Russian Federation | RU | RUS |
Russia | RU | RUS |
Rwanda | RW | RWA |
Saint Helena, Ascension and Tristan da Cunha | SH | SHN |
Saint Kitts and Nevis | KN | KNA |
Saint Lucia | LC | LCA |
Saint Pierre and Miquelon | PM | SPM |
Saint Vincent and the Grenadines | VC | VCT |
Saint Vincent & the Grenadines | VC | VCT |
St. Vincent and the Grenadines | VC | VCT |
Samoa | WS | WSM |
San Marino | SM | SMR |
Sao Tome and Principe | ST | STP |
Saudi Arabia | SA | SAU |
Senegal | SN | SEN |
Serbia | RS | SRB |
Seychelles | SC | SYC |
Sierra Leone | SL | SLE |
Singapore | SG | SGP |
Slovakia | SK | SVK |
Slovenia | SI | SVN |
Solomon Islands | SB | SLB |
Somalia | SO | SOM |
South Africa | ZA | ZAF |
South Georgia and the South Sandwich Islands | GS | SGS |
South Sudan | SS | SSD |
Spain | ES | ESP |
Sri Lanka | LK | LKA |
Sudan | SD | SDN |
Suriname | SR | SUR |
Svalbard and Jan Mayen | SJ | SJM |
Swaziland | SZ | SWZ |
Sweden | SE | SWE |
Switzerland | CH | CHE |
Syrian Arab Republic | SY | SYR |
Taiwan, Province of China | TW | TWN |
Taiwan | TW | TWN |
Tajikistan | TJ | TJK |
Tanzania, United Republic of | TZ | TZA |
Thailand | TH | THA |
Timor-Leste | TL | TLS |
Togo | TG | TGO |
Tokelau | TK | TKL |
Tonga | TO | TON |
Trinidad and Tobago | TT | TTO |
Tunisia | TN | TUN |
Turkey | TR | TUR |
Turkmenistan | TM | TKM |
Turks and Caicos Islands | TC | TCA |
Tuvalu | TV | TUV |
Uganda | UG | UGA |
Ukraine | UA | UKR |
United Arab Emirates | AE | ARE |
United Kingdom | GB | GBR |
United States | US | USA |
United States Minor Outlying Islands | UM | UMI |
Uruguay | UY | URY |
Uzbekistan | UZ | UZB |
Vanuatu | VU | VUT |
Venezuela, Bolivarian Republic of | VE | VEN |
Venezuela | VE | VEN |
Viet Nam | VN | VNM |
Vietnam | VN | VNM |
Virgin Islands, British | VG | VGB |
Virgin Islands, U.S. | VI | VIR |
Wallis and Futuna | WF | WLF |
Western Sahara | EH | ESH |
Yemen | YE | YEM |
Zambia | ZM | ZMB |
Zimbabwe | ZW | ZWE |
3. Use the VLOOKUP function
Finally, in your original sheet that includes all of the data, create a new column for country names. Use VLOOKUP to find the respective country name in the second sheet (Sheet2) by providing the country code present in the first sheet (Sheet1).
Syntax: VLOOKUP(search_key, range, index, [is_sorted])
Search Key: The key that should be used to search for the desired value. In this case, the cell number containing the country code, “A2”
Range: The range of columns that should be searched to find our desired value. We need to give the sheet name, the key column that matches our search key, and the column that should return the desired value. In our case, “Sheet2!A:B”, where Sheet 2 is the name of the sheet in which our country code to country name mapping is. Column A contains the Codes (Keys) while Column B contains the Country Names (Desired Values)
Index: The exact column number within the range that includes our desired value. In our case, “2” because Column B is the second column in the range. (Index starts with number 1)
Is Sorted: It should be used when the data is in a sorted format. In our case, we would “False”
Hence the complete formula will become like this:
=VLOOKUP(A2, Sheet2!A:B, 2, False)
Use this formula for the first row and then double-click on the corner of the cell to automatically populate this formula with respective values to the whole column and you will be good to go.
4. Import Data
You have now the Country Names. Use this data to import records with correct country mappings into Salesforce and let your client know that the job is done.
That is all. Now you would be able to always convert Country Code to Country Name in a spreadsheet. If you liked this guide, please share it with fellow developers. You might also want to read how to generate Excel file in the aura component.
Keep visiting this blog to expand your knowledge of Salesforce.
Resources
Also Read
- How to find Salesforce org type via Apex code?
- How to add cross-object fields as merge fields in Lightning Email Template
- How to implement Grid System using CSS in VF page with renderAs PDF