Excel - replace multiple strings in a single cell, based upon a lookup table

Question

I would like to do a replace on multiple strings within a cell, based upon a lookup table.

My lookup table has two columns:

AUSTRIA | AT
BRAZIL  | BR
COLOMBIA| CO

My data table has two columns:

Apples | AUSTRIA,COLOMBIA
Banana | BRAZIL,AUSTRIA

I want the values in the data table to be replaced by the values in the lookup table (which, being every ISO country code, is quite long).

Desired output:

Apples | AT,CO
Banana | BR,AT

Any ideas? Avoiding VBA is preferable!

Answer 1

In case you decide to go there (it'll be fast, like pulling off a band-aid), here's a VBA solution. Just make the necessary changes noted in the comments to make this work for your workbook.

Sub abbrev()

Dim abvtab() As Variant
Dim ltsheet As Worksheet
Dim datasheet As Worksheet
Dim lt As Range

'Change Lookup to the sheet name with your lookup table.
Set ltsheet = Sheets("Lookup")

'Change Data to the sheet name with your data.
Set datasheet = Sheets("Data")

'Change A2 to the top left cell (not the header) in your lookup table.
'Change B2 to top right cell.
Set lt = ltsheet.Range("A2", ltsheet.Range("B2").End(xlDown))

abvtab = lt

For i = 1 To UBound(abvtab)
    datasheet.Cells.Replace What:=abvtab(i, 1), Replacement:=abvtab(i, 2), LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
        ReplaceFormat:=False
Next i

End Sub