今天碰到了这个需求，Excel中输入中文国家名，自动转成英文。

如果是用Google Spreadsheet就非常简单，因为它自带一个GoogleTranslate的函数，比如繁体转简体，用下面这个公式即可：

=GOOGLETRANSLATE(A1,"zh-tw","zh-cn")

从英文翻译成中文也很简单：

=GOOGLETRANSLATE(A2, "en", "zh-cn")

有人做了一个简单的翻译模版，甚至可以把中文转成拼音，你可以通过File → Make a copy拿来直接自己用。

不过Excel就复杂多了，没有现成的工具可用。只好自己动手做了一个：

Option Explicit
'Main Function
Function CountryTranslate(ByVal CountryName)
' get CountryName
CountryName = Trim(CountryName)
' translate CountryName into english

Select Case CountryName
Case "阿富汗"
CountryName = "Afghanistan"
Case "阿尔巴尼亚"
CountryName = "Albania"
Case "阿尔及利亚"
CountryName = "Algeria"
Case "美属萨摩亚"
CountryName = "American Samoa"
Case "安道尔"
CountryName = "Andorra"
Case "安哥拉"
CountryName = "Angola"
Case "安圭拉"
CountryName = "Anguilla"
Case "南极洲"
CountryName = "Antarctica"
Case "安提瓜和巴布达"
CountryName = "Antigua and Barbuda"
Case "阿根廷"
CountryName = "Argentina"
Case "亚美尼亚"
CountryName = "Armenia"
Case "阿鲁巴"
CountryName = "Aruba"
Case "澳大利亚"
CountryName = "Australia"
Case "奥地利"
CountryName = "Austria"
Case "阿塞拜疆"
CountryName = "Azerbaijan"
Case "巴哈马"
CountryName = "Bahamas"
Case "巴林"
CountryName = "Bahrain"
Case "孟加拉国"
CountryName = "Bangladesh"
Case "巴巴多斯"
CountryName = "Barbados"
Case "白俄罗斯"
CountryName = "Belarus"
Case "比利时"
CountryName = "Belgium"
Case "伯利兹"
CountryName = "Belize"
Case "贝宁"
CountryName = "Benin"
Case "百慕达群岛"
CountryName = "Bermuda"
Case "不丹"
CountryName = "Bhutan"
Case "玻利维亚"
CountryName = "Bolivia"
Case "波斯尼亚 - 黑塞哥维那"
CountryName = "Bosnia and Herzegovina"
Case "博茨瓦纳"
CountryName = "Botswana"
Case "巴西"
CountryName = "Brazil"
Case "文莱达鲁萨兰国"
CountryName = "Brunei Darussalam"
Case "保加利亚"
CountryName = "Bulgaria"
Case "布基纳法索"
CountryName = "Burkina Faso"
Case "蒲隆地"
CountryName = "Burundi"
Case "柬埔寨"
CountryName = "Cambodia"
Case "喀麦隆"
CountryName = "Cameroon"
Case "加拿大"
CountryName = "Canada"
Case "佛得角"
CountryName = "Cape Verde"
Case "中非共和国"
CountryName = "Central African Republic"
Case "乍得"
CountryName = "Chad"
Case "智利"
CountryName = "Chile"
Case "中国, 中华"
CountryName = "China"
Case "哥伦比亚"
CountryName = "Colombia"
Case "葛摩"
CountryName = "Comoros"
Case "刚果民主共和国"
CountryName = "Congo, Dem. Rep. (Kinshasa)"
Case "刚果共和国"
CountryName = "Congo, Republic of (Brazzaville)"
Case "哥斯达黎加"
CountryName = "Costa Rica"
Case "科特迪瓦"
CountryName = "Côte d'Ivoire (Ivory Coast)"
Case "克罗地亚"
CountryName = "Croatia"
Case "古巴"
CountryName = "Cuba"
Case "塞浦路斯"
CountryName = "Cyprus"
Case "捷克"
CountryName = "Czechia"
Case "丹麦"
CountryName = "Denmark"
Case "吉布提"
CountryName = "Djibouti"
Case "多米尼克"
CountryName = "Dominica"
Case "多米尼加共和国"
CountryName = "Dominican Republic"
Case "东帝汶"
CountryName = "East Timor Timor-Leste"
Case "厄瓜多尔"
CountryName = "Ecuador"
Case "埃及"
CountryName = "Egypt"
Case "萨尔瓦多"
CountryName = "El Salvador"
Case "赤道几内亚"
CountryName = "Equatorial Guinea"
Case "厄立特里亚"
CountryName = "Eritrea"
Case "爱沙尼亚"
CountryName = "Estonia"
Case "埃塞俄比亚"
CountryName = "Ethiopia"
Case "法罗群岛"
CountryName = "Faroe Islands"
Case "斐济"
CountryName = "Fiji"
Case "芬兰"
CountryName = "Finland"
Case "法国"
CountryName = "France"
Case "法属圭亚那"
CountryName = "French Guiana"
Case "法属波利尼西亚"
CountryName = "French Polynesia"
Case "加蓬"
CountryName = "Gabon"
Case "冈比亚"
CountryName = "Gambia"
Case "格鲁吉亚"
CountryName = "Georgia"
Case "德国"
CountryName = "Germany"
Case "加纳"
CountryName = "Ghana"
Case "英国"
CountryName = "Great Britain"
Case "希腊"
CountryName = "Greece"
Case "格陵兰"
CountryName = "Greenland"
Case "格林纳达"
CountryName = "Grenada"
Case "瓜德罗普"
CountryName = "Guadeloupe"
Case "关岛"
CountryName = "Guam"
Case "危地马拉"
CountryName = "Guatemala"
Case "几内亚"
CountryName = "Guinea"
Case "几内亚比索"
CountryName = "Guinea-Bissau"
Case "圭亚那"
CountryName = "Guyana"
Case "海地"
CountryName = "Haiti"
Case "梵帝冈"
CountryName = "Holy See"
Case "洪都拉斯"
CountryName = "Honduras"
Case "香港"
CountryName = "Hong Kong"
Case "匈牙利"
CountryName = "Hungary"
Case "冰岛"
CountryName = "Iceland"
Case "印度"
CountryName = "India"
Case "印度尼西亚"
CountryName = "Indonesia"
Case "印尼"
CountryName = "Indonesia"
Case "伊朗"
CountryName = "Iran"
Case "伊拉克"
CountryName = "Iraq"
Case "爱尔兰"
CountryName = "Ireland"
Case "以色列"
CountryName = "Israel"
Case "意大利"
CountryName = "Italy"
Case "牙买加"
CountryName = "Jamaica"
Case "日本"
CountryName = "Japan"
Case "约旦"
CountryName = "Jordan"
Case "哈萨克斯坦"
CountryName = "Kazakhstan"
Case "肯尼亚"
CountryName = "Kenya"
Case "基里巴斯"
CountryName = "Kiribati"
Case "朝鲜"
CountryName = "North Korea"
Case "大韩民国"
CountryName = "South Korea"
Case "韩国"
CountryName = "South Korea"
Case "科威特"
CountryName = "Kuwait"
Case "吉尔吉斯斯坦"
CountryName = "Kyrgyzstan"
Case "老挝"
CountryName = "Laos"
Case "拉脱维亚"
CountryName = "Latvia"
Case "黎巴嫩"
CountryName = "Lebanon"
Case "莱索托"
CountryName = "Lesotho"
Case "利比里亚"
CountryName = "Liberia"
Case "利比亚"
CountryName = "Libya"
Case "列支敦士登"
CountryName = "Liechtenstein"
Case "立陶宛"
CountryName = "Lithuania"
Case "卢森堡"
CountryName = "Luxembourg"
Case "澳门"
CountryName = "Macau"
Case "北马其顿"
CountryName = "North Macedonia"
Case "马达加斯加"
CountryName = "Madagascar"
Case "马拉维"
CountryName = "Malawi"
Case "马来西亚"
CountryName = "Malaysia"
Case "马尔代夫"
CountryName = "Maldives"
Case "马里"
CountryName = "Mali"
Case "马耳他"
CountryName = "Malta"
Case "马绍尔群岛"
CountryName = "Marshall Islands"
Case "马提尼克"
CountryName = "Martinique"
Case "毛里塔尼亚"
CountryName = "Mauritania"
Case "毛里求斯"
CountryName = "Mauritius"
Case "墨西哥"
CountryName = "Mexico"
Case "密克罗尼西亚联邦"
CountryName = "Micronesia, Federated States of Micronesia"
Case "摩尔多瓦"
CountryName = "Moldova"
Case "摩纳哥"
CountryName = "Monaco"
Case "蒙古"
CountryName = "Mongolia"
Case "黑山"
CountryName = "Montenegro"
Case "蒙塞拉特岛"
CountryName = "Montserrat"
Case "摩洛哥"
CountryName = "Morocco"
Case "莫桑比克"
CountryName = "Mozambique"
Case "缅甸"
CountryName = "Myanmar, Burma"
Case "纳米比亚"
CountryName = "Namibia"
Case "瑙鲁"
CountryName = "Nauru"
Case "尼泊尔"
CountryName = "Nepal"
Case "荷兰"
CountryName = "Netherlands"
Case "新喀里多尼亚"
CountryName = "New Caledonia"
Case "新西兰"
CountryName = "New Zealand"
Case "尼加拉瓜"
CountryName = "Nicaragua"
Case "尼日尔"
CountryName = "Niger"
Case "尼日利亚"
CountryName = "Nigeria"
Case "挪威"
CountryName = "Norway"
Case "阿曼"
CountryName = "Oman"
Case "巴基斯坦"
CountryName = "Pakistan"
Case "帕劳"
CountryName = "Palau"
Case "巴勒斯坦"
CountryName = "Palestine, State of"
Case "巴拿马"
CountryName = "Panama"
Case "巴布亚新几内亚"
CountryName = "Papua New Guinea"
Case "巴拉圭"
CountryName = "Paraguay"
Case "秘鲁"
CountryName = "Peru"
Case "菲律宾"
CountryName = "Philippines"
Case "波兰"
CountryName = "Poland"
Case "葡萄牙"
CountryName = "Portugal"
Case "波多黎各"
CountryName = "Puerto Rico"
Case "卡塔尔"
CountryName = "Qatar"
Case "留尼汪"
CountryName = "Réunion"
Case "罗马尼亚"
CountryName = "Romania"
Case "俄罗斯"
CountryName = "Russia"
Case "卢旺达"
CountryName = "Rwanda"
Case "圣基茨和尼维斯"
CountryName = "Saint Kitts and Nevis"
Case "圣卢西亚"
CountryName = "Saint Lucia"
Case "圣文森特和格林纳丁斯"
CountryName = "Saint Vincent and the Grenadines"
Case "萨摩亚"
CountryName = "Samoa"
Case "圣马力诺"
CountryName = "San Marino"
Case "圣多美普林西比"
CountryName = "São Tomé and Príncipe"
Case "沙特阿拉伯"
CountryName = "Saudi Arabia"
Case "塞内加尔"
CountryName = "Senegal"
Case "塞尔维亚"
CountryName = "Serbia"
Case "塞舌尔"
CountryName = "Seychelles"
Case "塞拉利昂"
CountryName = "Sierra Leone"
Case "新加坡"
CountryName = "Singapore"
Case "斯洛伐克"
CountryName = "Slovakia"
Case "斯洛文尼亚"
CountryName = "Slovenia"
Case "所罗门群岛"
CountryName = "Solomon Islands"
Case "索马里"
CountryName = "Somalia"
Case "南非"
CountryName = "South Africa"
Case "南苏丹"
CountryName = "South Sudan"
Case "西班牙"
CountryName = "Spain"
Case "斯里兰卡"
CountryName = "Sri Lanka"
Case "苏丹"
CountryName = "Sudan"
Case "苏里南"
CountryName = "Suriname"
Case "史瓦济兰"
CountryName = "Swaziland"
Case "瑞典"
CountryName = "Sweden"
Case "瑞士"
CountryName = "Switzerland"
Case "叙利亚"
CountryName = "Syria"
Case "台湾"
CountryName = "Taiwan"
Case "塔吉克斯坦"
CountryName = "Tajikistan"
Case "坦桑尼亚"
CountryName = "Tanzania"
Case "泰国"
CountryName = "Thailand"
Case "东帝汶"
CountryName = "Timor-Leste"
Case "多哥"
CountryName = "Togo"
Case "东加"
CountryName = "Tonga"
Case "特立尼达和多巴哥"
CountryName = "Trinidad and Tobago"
Case "突尼斯"
CountryName = "Tunisia"
Case "土耳其"
CountryName = "Turkey"
Case "土库曼"
CountryName = "Turkmenistan"
Case "图瓦卢"
CountryName = "Tuvalu"
Case "乌干达"
CountryName = "Uganda"
Case "乌克兰"
CountryName = "Ukraine"
Case "阿拉伯联合酋长国"
CountryName = "United Arab Emirates"
Case "英国"
CountryName = "United Kingdom"
Case "美国"
CountryName = "United States"
Case "乌拉圭"
CountryName = "Uruguay"
Case "乌兹别克斯坦"
CountryName = "Uzbekistan"
Case "瓦努阿图"
CountryName = "Vanuatu"
Case "梵帝冈"
CountryName = "Vatican City State"
Case "委内瑞拉"
CountryName = "Venezuela"
Case "越南"
CountryName = "Vietnam"
Case "也门"
CountryName = "Yemen"
Case "赞比亚"
CountryName = "Zambia"
Case "津巴布韦"
CountryName = "Zimbabwe"
Case Else
CountryName = "-"
End Select

CountryTranslate = CountryName
End Function

国家名不一定全，自己可以DIY。

公式使用方法：

=CountryTranslate(A1)

至于如何插入这段代码到Excel，可以看这篇文章。