Replacing multiple text within same cell

[Country_Calc_2]

I need to replace multiple text values within a single cell as shown below. In this case the separator is a semicolon.. The replacement values would be in a separate list with example below.

Any help would be greatly appreciated.

Before	After
United States;United Kingdom;France;Germany;Israel;Italy;Spain	USA, GBR, FRA, DEU, ISR, ITA, ESP

Definition	Code Value
France	FRA
Germany	DEU
Israel	ISR
Italy	ITA
Spain	ESP
United Kingdom	GBR
United States	USA

 

[awoohaw]

See if this works for you:

Book1
	A	B
1	Before	After
2	United States;United Kingdom;France;Germany;Israel;Italy;Spain	USA, GBR, FRA, DEU, ISR, ITA, ESP
3
4
5	Definition	Code Value
6	France	FRA
7	Germany	DEU
8	Israel	ISR
9	Italy	ITA
10	Spain	ESP
11	United Kingdom	GBR
12	United States	USA
13
14
15	United States;United Kingdom;France;Germany;Israel;Italy;Spain	USA,GBR,FRA,DEU,ISR,ITA,ESP
Sheet2

Cell Formulas
Range	Formula
B15	B15	=LET(FullNames,TEXTSPLIT(A15,";"),FullNameList,$A$6:$A$12,FullNameShort,$B$6:$B$12, Shorts,BYCOL(FullNames,LAMBDA(Fullnames,XLOOKUP(Fullnames,FullNameList,FullNameShort,"notfound",0,1))), TEXTJOIN(",",TRUE,Shorts))

 

[Country_Calc_2]

This does work thanks. I have to have my list on another worksheet so I used a named range using the same names. This is probably not ideal but it works. Thanks again.

=LET(FullNames,TEXTSPLIT(Y4,";"),FullNameList,FullNameList,FullNameShort,FullNameShort,
Shorts,BYCOL(FullNames,LAMBDA(Fullnames,XLOOKUP(Fullnames,FullNameList,FullNameShort,"notfound",0,1))),
TEXTJOIN(", ",TRUE,Shorts))

 

[awoohaw]

happy to help.

 

[Peter_SSs]

This does work thanks.

Since post 2 effectively answered your question the marked solution has been changed accordingly.

 

[awoohaw]

Since post 2 effectively answered your question the marked solution has been changed accordingly.

Thank you Peter

 

[Peter_SSs]

Does the problem need such a complicated formula? Wouldn't this be sufficient?

24 03 02.xlsm
	A	B
5	Definition	Code Value
6	France	FRA
7	Germany	DEU
8	Israel	ISR
9	Italy	ITA
10	Spain	ESP
11	United Kingdom	GBR
12	United States	USA
13
14
15	United States;United Kingdom;France;Germany;Israel;Italy;Spain	USA,GBR,FRA,DEU,ISR,ITA,ESP
Abbreviations

Cell Formulas
Range	Formula
B15	B15	=TEXTJOIN(",",,XLOOKUP(TEXTSPLIT(A15,";"),A6:A12,B6:B12))