CODEWITHSUNDEEP
excelexcel-formula
zabumba
zabumba

Reputation: 12412

Excel Substitute a substring in cell (sheet A) by the corresponding string in "lookup" (sheet B)

How to do this in MS Excel 15.4

I want to process Column A to become Column B

Column A                  | Column B
----------------------------------------------------------------------
one, 1, two, 2, three, 3  | one apple, two bananas, three strawberries
one, 1, four, 4           | one apple, four oranges
.......................
... many other rows ...
.......................
two, 2, four, 4, three, 3 | two bananas, four oranges, three strawberries

The Column A can have n matching substrings in the lookup sheet.

I have another sheet (lookup table) with what to substitute the text in Column A with

Match col | Replace col
----------------------------
one, 1    | one apple
two, 2    | two bananas
three, 3  | three strawberries
four, 4   | four oranges
... and many more ...

I want to replace all the substrings found in Column A with the Replace col value of the lookup table

It looks like I may be able to combine VLOOKUP with SUBSTITUTE, but I am struggling with it

Upvotes: 1

Views: 599

Answers (2)

Scott Craner
Scott Craner

Reputation: 152505

To do it in indivdual cells;

=IFERROR(VLOOKUP(TRIM(SUBSTITUTE(MID(SUBSTITUTE($A1,",",REPT(" ",999)),(COLUMN(A:A)-1)*2*999+1,2*999),REPT(" ",999),",")),Sheet2!$A:$B,2,FALSE),"")

enter image description here

If you have a subscription to Office 3651 excel you can use this array formula to put it all in one cell:

=TEXTJOIN(",",TRUE,IFERROR(LOOKUP(TRIM(SUBSTITUTE(MID(SUBSTITUTE($A1,",",REPT(" ",999)),(ROW(INDIRECT("1:" & INT((LEN($A1)-LEN(SUBSTITUTE($A1,",","")))/2)+1))-1)*2*999+1,2*999),REPT(" ",999),",")),Sheet2!A:A,Sheet2!B:B),""))

Being an array formula it must be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode. If done correctly then Excel will put {} around the formula.

On caveat is that the reference data must be sorted on the lookup column:

enter image description here

enter image description here

1 If you do not have Office 365 but want to use this formula you can place the code from my answer HERE that will mimic the TEXTJOIN() in a module attached to the worksheet. Then use the formula as described above

Upvotes: 2

BruceWayne
BruceWayne

Reputation: 23283

I have a rather clunky solution, but it'll work for you if you don't mind taking perhaps a few extra steps. (No VBA required).

With your original data, highlight all of it and do Text to Columns with a comma delimiter. Set the destination to wherever you like. I chose the column just right of it (so, B2):

enter image description here

So now you have it all split up.

I put the VLOOKUP() table in "Sheet2":

enter image description here

And back on Sheet1, in I2, I used this formula:
=IFERROR(VLOOKUP(TRIM(B2)&", "&TRIM(C2),Sheet2!$A$1:$B$4,2,FALSE),"")

And drag right. You'll have some empty columns which you can hide/Delete, then copy all the data.

enter image description here

Upvotes: 2

Related Questions