Replace multiple text within a single cell

[Country_Calc]

I need to change 2 letter country codes to the full country name, but have many countries within a single cell.

Table below shows the desired output. But it could have 30 country codes in a single cell.

A	B
AD, AO	Andorra, Angola
AO, AI	Angola, Aguilla

It would be nice to use a formula to reference a table that has all of these, but I could also do manually if needed. Example of list is below.

2 letter code​	Country​
AD​	Andorra​
AO​	Angola​
AI​	Anguilla​

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Country_Calc]

Thanks, I have updated and am using 365.

 

[alansidman]

I used Power Query to achieve your expected results.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
    #"Split Column by Delimiter1" = Table.ExpandListColumn(Table.TransformColumns(#"Split Column by Delimiter", {{"Column2", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column2"),
    #"Trimmed Text" = Table.TransformColumns(#"Split Column by Delimiter1",{{"Column1", Text.Trim, type text}, {"Column2", Text.Trim, type text}}),
    #"Removed Duplicates" = Table.Distinct(#"Trimmed Text")
in
    #"Removed Duplicates"

Split by Delimiter into Rows (and Columns) with Power Query - Excel Campus

You can take a string of emails and turn them into an organized table of data using Power Query to split by delimiters.

www.excelcampus.com

 

[JW00]

Here's an alternative, "=CountryLookUp(A2)", you just have to separate the two letter codes with a comma followed by a space (", ") as the delimiter.

Codes	Country
AF, DZ	Afghanistan, Algeria
AI, BH, BY	Anguilla, Bahrain, Belarus
AS, BV, CN	American Samoa, Bouvet Island, China

Alpha-2 code	Country
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 (the)
BH	Bahrain
BD	Bangladesh
BB	Barbados
BY	Belarus
BE	Belgium
BZ	Belize
BJ	Benin
BM	Bermuda
BT	Bhutan
BO	Bolivia (Plurinational State of)
BQ	Bonaire, Sint Eustatius and Saba
BA	Bosnia and Herzegovina
BW	Botswana
BV	Bouvet Island
BR	Brazil
IO	British Indian Ocean Territory (the)
BN	Brunei Darussalam
BG	Bulgaria
BF	Burkina Faso
BI	Burundi
CV	Cabo Verde
KH	Cambodia
CM	Cameroon
CA	Canada
KY	Cayman Islands (the)
CF	Central African Republic (the)
TD	Chad
CL	Chile
CN	China
CX	Christmas Island
CC	Cocos (Keeling) Islands (the)
CO	Colombia
KM	Comoros (the)
CD	Congo (the Democratic Republic of the)
CG	Congo (the)
CK	Cook Islands (the)
CR	Costa Rica
HR	Croatia
CU	Cuba
CW	Curaçao
CY	Cyprus
CZ	Czechia
CI	Côte d'Ivoire
DK	Denmark
DJ	Djibouti
DM	Dominica
DO	Dominican Republic (the)
EC	Ecuador
EG	Egypt
SV	El Salvador
GQ	Equatorial Guinea
ER	Eritrea
EE	Estonia
SZ	Eswatini
ET	Ethiopia
FK	Falkland Islands (the) [Malvinas]
FO	Faroe Islands (the)
FJ	Fiji
FI	Finland
FR	France
GF	French Guiana
PF	French Polynesia
TF	French Southern Territories (the)
GA	Gabon
GM	Gambia (the)
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 (the)
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 (the Democratic People's Republic of)
KR	Korea (the Republic of)
KW	Kuwait
KG	Kyrgyzstan
LA	Lao People's Democratic Republic (the)
LV	Latvia
LB	Lebanon
LS	Lesotho
LR	Liberia
LY	Libya
LI	Liechtenstein
LT	Lithuania
LU	Luxembourg
MO	Macao
MG	Madagascar
MW	Malawi
MY	Malaysia
MV	Maldives
ML	Mali
MT	Malta
MH	Marshall Islands (the)
MQ	Martinique
MR	Mauritania
MU	Mauritius
YT	Mayotte
MX	Mexico
FM	Micronesia (Federated States of)
MD	Moldova (the Republic of)
MC	Monaco
MN	Mongolia
ME	Montenegro
MS	Montserrat
MA	Morocco
MZ	Mozambique
MM	Myanmar
NA	Namibia
NR	Nauru
NP	Nepal
NL	Netherlands (the)
NC	New Caledonia
NZ	New Zealand
NI	Nicaragua
NE	Niger (the)
NG	Nigeria
NU	Niue
NF	Norfolk Island
MP	Northern Mariana Islands (the)
NO	Norway
OM	Oman
PK	Pakistan
PW	Palau
PS	Palestine, State of
PA	Panama
PG	Papua New Guinea
PY	Paraguay
PE	Peru
PH	Philippines (the)
PN	Pitcairn
PL	Poland
PT	Portugal
PR	Puerto Rico
QA	Qatar
MK	Republic of North Macedonia
RO	Romania
RU	Russian Federation (the)
RW	Rwanda
RE	Réunion
BL	Saint Barthélemy
SH	Saint Helena, Ascension and Tristan da Cunha
KN	Saint Kitts and Nevis
LC	Saint Lucia
MF	Saint Martin (French part)
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
SX	Sint Maarten (Dutch part)
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 (the)
SR	Suriname
SJ	Svalbard and Jan Mayen
SE	Sweden
CH	Switzerland
SY	Syrian Arab Republic
TW	Taiwan (Province of China)
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 (the)
TV	Tuvalu
UG	Uganda
UA	Ukraine
AE	United Arab Emirates (the)
GB	United Kingdom of Great Britain and Northern Ireland (the)
UM	United States Minor Outlying Islands (the)
US	United States of America (the)
UY	Uruguay
UZ	Uzbekistan
VU	Vanuatu
VE	Venezuela (Bolivarian Republic of)
VN	Viet Nam
VG	Virgin Islands (British)
VI	Virgin Islands (U.S.)
WF	Wallis and Futuna
EH	Western Sahara
YE	Yemen
ZM	Zambia
ZW	Zimbabwe
AX	Åland Islands

Function CountryLookUp(rng As Range)
Dim rng1 As Range
Dim arr() As String, arr2() As Variant
Dim TempStr As String
Dim i As Long, j As Long


    If InStr(rng, ",") > 0 Then
        arr = Split(rng, ", ")
    Else
        arr = rng
    End If
    
    Set rng1 = Sheets("Country_List").Range("A2:B250")
    arr2 = rng1.Value
    
    For j = LBound(arr) To UBound(arr)
        For i = LBound(arr2) To UBound(arr2)
            If arr(j) = arr2(i, 1) And Len(TempStr) > 0 Then
               TempStr = TempStr & ", " & arr2(i, 2)
               GoTo Skip
            ElseIf arr(j) = arr2(i, 1) Then
               TempStr = arr2(i, 2)
               GoTo Skip
            End If
        Next i
Skip:
    Next j
            
    CountryLookUp = TempStr

End Function

 

[Fluff]

Another option

+Fluff 1.xlsm
	A	B	C	D	E
1	Alpha-2 code	Country
2	AD	Andorra		CA, AG, AU	Canada, Antigua and Barbuda, Australia
3	AE	United Arab Emirates (the)		BR, AL, BM, AZ, AD	Brazil, Albania, Bermuda, Azerbaijan, Andorra
4	AF	Afghanistan
5	AG	Antigua and Barbuda
6	AI	Anguilla
7	AL	Albania
8	AM	Armenia
9	AO	Angola
10	AQ	Antarctica
11	AR	Argentina
12	AS	American Samoa
13	AT	Austria
14	AU	Australia
15	AW	Aruba
16	AX	Åland Islands
17	AZ	Azerbaijan
18	BA	Bosnia and Herzegovina
19	BB	Barbados
20	BD	Bangladesh
21	BE	Belgium
22	BF	Burkina Faso
23	BG	Bulgaria
24	BH	Bahrain
25	BI	Burundi
26	BJ	Benin
27	BL	Saint Barthélemy
28	BM	Bermuda
29	BN	Brunei Darussalam
30	BO	Bolivia (Plurinational State of)
31	BQ	Bonaire, Sint Eustatius and Saba
32	BR	Brazil
33	BS	Bahamas (the)
34	BT	Bhutan
35	BV	Bouvet Island
36	BW	Botswana
37	BY	Belarus
38	BZ	Belize
39	CA	Canada
40	CC	Cocos (Keeling) Islands (the)
Lists

Cell Formulas
Range	Formula
E2:E3	E2	=LET(f,FILTERXML("<k><m>"&SUBSTITUTE(D2,", ","</m><m>")&"</m></k>","//m"),a,FILTER($A$2:$B$300,ISNUMBER(MATCH($A$2:$A$300,f,0))),TEXTJOIN(", ",,INDEX(SORTBY(a,MATCH(INDEX(a,,1),f,0)),,2)))

 

[Country_Calc]

Thanks everyone for the 3 different options. I am sure all of them worked, but Fluff's option was the one I was most comfortable with and tested. I tried to get it to work with referencing the table in Columns A and B on another worksheet with a named range and I could not get it to work.

It worked by setting up the column order exactly as shown. I just pasted as text and deleted after setting up.

Thanks again.

 

[Fluff]

Is column A & B a named range or a structured table?

 

[Country_Calc]

column A & B are a named ranged on another worksheet

 

[Fluff]

Ok how about

+Fluff 1.xlsm
	A	B
1
2	CA, AG, AU	Canada, Antigua and Barbuda, Australia
3	BR, AL, BM, AZ, AD	Brazil, Albania, Bermuda, Azerbaijan, Andorra
Sheet2

Cell Formulas
Range	Formula
B2:B3	B2	=LET(f,FILTERXML("<k><m>"&SUBSTITUTE(A2,", ","</m><m>")&"</m></k>","//m"),a,FILTER(Countries,ISNUMBER(MATCH(INDEX(Countries,,1),f,0))),TEXTJOIN(", ",,INDEX(SORTBY(a,MATCH(INDEX(a,,1),f,0)),,2)))

Named Ranges
Name	Refers To	Cells
Countries	=Sheet1!$A$2:$B$40	B2:B3