CODEWITHSUNDEEP
google-sheets
Thomas Evensen
Thomas Evensen

Reputation: 37

Replace several parts of a string with values from a lookup table

I have a cell with text: "aaa,bbb,ccc,ddd,eee".

I have a lookup table with values corresponding to the different strings. E.g. "aaa"=1, "bbb"=2 etc.

I need to create a single cell with text where the "aaa" etc. have been replaces with their corresponding value.

E.g. the string "aaa,bbb" should create the string "1,2".

Any way to do this?

Upvotes: 1

Views: 604

Answers (1)

Chris Hick
Chris Hick

Reputation: 3094

If your lookup table is in columns A:B and the text you want to replace in cell C1 then try:

=JOIN(",",ArrayFormula(VLOOKUP(SPLIT(C1,","),A:B,2,0)))

Upvotes: 1

Related Questions