Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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!

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
add comment

1 Answer

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, _
        ReplaceFormat:=False
Next i

End Sub
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.