Take the 2-minute tour ×

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

My lookup table has two columns:


My data table has two columns:


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!

share|improve this question
Without VBA this task becomes a tad more difficult. You can use the SUBSTITUTE function, as well as the VLOOKUP function to get what you want. Alternatively, you can also use the LEN, REPLACE, and FIND functions. –  Breakthrough Jun 24 '11 at 10:27

1 Answer 1

up vote 2 down vote accepted

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, _
Next i

End Sub
share|improve this answer

Your Answer


