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​

 

[Country_Calc]

The reference to another worksheet works now and would probably be ideal for users having similar issues. There is 1 minor issue in that if the cell only has 1 value ex. "CA" it returns a #CALC error. But since this only affects cells with a single value it is a very quick manual fix from there.

 

[Fluff]

If you have only one code, you can use

=LET(f,FILTERXML("<k><m>"&SUBSTITUTE(A2,", ","</m><m>")&"</m></k>","//m"),r,ROWS(f),a,FILTER(Countries,ISNUMBER(MATCH(INDEX(Countries,,1),f,0))),IF(r=1, INDEX(INDEX(Countries,,2),MATCH(A2,INDEX(Countries,,1),0)),TEXTJOIN(", ",,INDEX(SORTBY(a,MATCH(INDEX(a,,1),f,0)),,2))))