Declarative

Convert Country Code to Country Name in Google Sheets for Data Import Wizard

Convert Country Code to Country Name

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:

Sheet2 - Country Code to Country Name Mapping
Sheet2 – Mapping of Country Code with Country Name

Dataset

CountryAlpha 2Alpha 3
AfghanistanAFAFG
AlbaniaALALB
AlgeriaDZDZA
American SamoaASASM
AndorraADAND
AngolaAOAGO
AnguillaAIAIA
AntarcticaAQATA
Antigua and BarbudaAGATG
ArgentinaARARG
ArmeniaAMARM
ArubaAWABW
AustraliaAUAUS
AustriaATAUT
AzerbaijanAZAZE
BahamasBSBHS
BahrainBHBHR
BangladeshBDBGD
BarbadosBBBRB
BelarusBYBLR
BelgiumBEBEL
BelizeBZBLZ
BeninBJBEN
BermudaBMBMU
BhutanBTBTN
Bolivia, Plurinational State ofBOBOL
BoliviaBOBOL
Bosnia and HerzegovinaBABIH
BotswanaBWBWA
Bouvet IslandBVBVT
BrazilBRBRA
British Indian Ocean TerritoryIOIOT
Brunei DarussalamBNBRN
BruneiBNBRN
BulgariaBGBGR
Burkina FasoBFBFA
BurundiBIBDI
CambodiaKHKHM
CameroonCMCMR
CanadaCACAN
Cape VerdeCVCPV
Cayman IslandsKYCYM
Central African RepublicCFCAF
ChadTDTCD
ChileCLCHL
ChinaCNCHN
Christmas IslandCXCXR
Cocos (Keeling) IslandsCCCCK
ColombiaCOCOL
ComorosKMCOM
CongoCGCOG
Congo, the Democratic Republic of theCDCOD
Cook IslandsCKCOK
Costa RicaCRCRI
Côte d’IvoireCICIV
Ivory CoastCICIV
CroatiaHRHRV
CubaCUCUB
CyprusCYCYP
Czech RepublicCZCZE
DenmarkDKDNK
DjiboutiDJDJI
DominicaDMDMA
Dominican RepublicDODOM
EcuadorECECU
EgyptEGEGY
El SalvadorSVSLV
Equatorial GuineaGQGNQ
EritreaERERI
EstoniaEEEST
EthiopiaETETH
Falkland Islands (Malvinas)FKFLK
Faroe IslandsFOFRO
FijiFJFJI
FinlandFIFIN
FranceFRFRA
French GuianaGFGUF
French PolynesiaPFPYF
French Southern TerritoriesTFATF
GabonGAGAB
GambiaGMGMB
GeorgiaGEGEO
GermanyDEDEU
GhanaGHGHA
GibraltarGIGIB
GreeceGRGRC
GreenlandGLGRL
GrenadaGDGRD
GuadeloupeGPGLP
GuamGUGUM
GuatemalaGTGTM
GuernseyGGGGY
GuineaGNGIN
Guinea-BissauGWGNB
GuyanaGYGUY
HaitiHTHTI
Heard Island and McDonald IslandsHMHMD
Holy See (Vatican City State)VAVAT
HondurasHNHND
Hong KongHKHKG
HungaryHUHUN
IcelandISISL
IndiaININD
IndonesiaIDIDN
Iran, Islamic Republic ofIRIRN
IraqIQIRQ
IrelandIEIRL
Isle of ManIMIMN
IsraelILISR
ItalyITITA
JamaicaJMJAM
JapanJPJPN
JerseyJEJEY
JordanJOJOR
KazakhstanKZKAZ
KenyaKEKEN
KiribatiKIKIR
Korea, Democratic People’s Republic ofKPPRK
Korea, Republic ofKRKOR
South KoreaKRKOR
KuwaitKWKWT
KyrgyzstanKGKGZ
Lao People’s Democratic RepublicLALAO
LatviaLVLVA
LebanonLBLBN
LesothoLSLSO
LiberiaLRLBR
Libyan Arab JamahiriyaLYLBY
LibyaLYLBY
LiechtensteinLILIE
LithuaniaLTLTU
LuxembourgLULUX
MacaoMOMAC
Macedonia, the former Yugoslav Republic ofMKMKD
MadagascarMGMDG
MalawiMWMWI
MalaysiaMYMYS
MaldivesMVMDV
MaliMLMLI
MaltaMTMLT
Marshall IslandsMHMHL
MartiniqueMQMTQ
MauritaniaMRMRT
MauritiusMUMUS
MayotteYTMYT
MexicoMXMEX
Micronesia, Federated States ofFMFSM
Moldova, Republic ofMDMDA
MonacoMCMCO
MongoliaMNMNG
MontenegroMEMNE
MontserratMSMSR
MoroccoMAMAR
MozambiqueMZMOZ
MyanmarMMMMR
BurmaMMMMR
NamibiaNANAM
NauruNRNRU
NepalNPNPL
NetherlandsNLNLD
Netherlands AntillesANANT
New CaledoniaNCNCL
New ZealandNZNZL
NicaraguaNINIC
NigerNENER
NigeriaNGNGA
NiueNUNIU
Norfolk IslandNFNFK
Northern Mariana IslandsMPMNP
NorwayNONOR
OmanOMOMN
PakistanPKPAK
PalauPWPLW
Palestinian Territory, OccupiedPSPSE
PanamaPAPAN
Papua New GuineaPGPNG
ParaguayPYPRY
PeruPEPER
PhilippinesPHPHL
PitcairnPNPCN
PolandPLPOL
PortugalPTPRT
Puerto RicoPRPRI
QatarQAQAT
RéunionREREU
RomaniaROROU
Russian FederationRURUS
RussiaRURUS
RwandaRWRWA
Saint Helena, Ascension and Tristan da CunhaSHSHN
Saint Kitts and NevisKNKNA
Saint LuciaLCLCA
Saint Pierre and MiquelonPMSPM
Saint Vincent and the GrenadinesVCVCT
Saint Vincent & the GrenadinesVCVCT
St. Vincent and the GrenadinesVCVCT
SamoaWSWSM
San MarinoSMSMR
Sao Tome and PrincipeSTSTP
Saudi ArabiaSASAU
SenegalSNSEN
SerbiaRSSRB
SeychellesSCSYC
Sierra LeoneSLSLE
SingaporeSGSGP
SlovakiaSKSVK
SloveniaSISVN
Solomon IslandsSBSLB
SomaliaSOSOM
South AfricaZAZAF
South Georgia and the South Sandwich IslandsGSSGS
South SudanSSSSD
SpainESESP
Sri LankaLKLKA
SudanSDSDN
SurinameSRSUR
Svalbard and Jan MayenSJSJM
SwazilandSZSWZ
SwedenSESWE
SwitzerlandCHCHE
Syrian Arab RepublicSYSYR
Taiwan, Province of ChinaTWTWN
TaiwanTWTWN
TajikistanTJTJK
Tanzania, United Republic ofTZTZA
ThailandTHTHA
Timor-LesteTLTLS
TogoTGTGO
TokelauTKTKL
TongaTOTON
Trinidad and TobagoTTTTO
TunisiaTNTUN
TurkeyTRTUR
TurkmenistanTMTKM
Turks and Caicos IslandsTCTCA
TuvaluTVTUV
UgandaUGUGA
UkraineUAUKR
United Arab EmiratesAEARE
United KingdomGBGBR
United StatesUSUSA
United States Minor Outlying IslandsUMUMI
UruguayUYURY
UzbekistanUZUZB
VanuatuVUVUT
Venezuela, Bolivarian Republic ofVEVEN
VenezuelaVEVEN
Viet NamVNVNM
VietnamVNVNM
Virgin Islands, BritishVGVGB
Virgin Islands, U.S.VIVIR
Wallis and FutunaWFWLF
Western SaharaEHESH
YemenYEYEM
ZambiaZMZMB
ZimbabweZWZWE
Data via GitHub Gist

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)

Sheet1 - Usage of VLOOKUP in Google Sheet
Sheet1 – Usage of VLOOKUP

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

  1. How to match data with VLOOKUP in Excel & Google Sheets
  2. Google Sheets VLOOKUP with examples

Also Read

Was this helpful?

Thanks for your feedback!

Talha Saqib

About Author

As a Salesforce Certified Developer, Talha Saqib is an expert in designing and implementing custom Salesforce solutions that help businesses achieve their goals. With 3+ years of experience in the Salesforce ecosystem, Talha has a deep understanding of the platform and is expert in Sales Cloud, Service Cloud, Experience Cloud, REST APIs, Aura and more.

Leave a comment

Your email address will not be published. Required fields are marked *

You may also like

correct format of DateTime field in Web-To-Lead form
Declarative

The correct format of DateTime field in Web-To-Lead form in Salesforce

So, I was recently working with a Web-To-Lead form in Salesforce and realized that all my values were being inserted
Declarative

How to add cross-object fields as merge fields in Lightning Email Template

Lightning Email Templates for sure are superior to their old counterpart, Classic Email Templates, however, they too come with certain