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

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

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

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

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