1

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

zabumba
  • 12,172
  • 16
  • 72
  • 129
  • 1
    What have you tried? Are you required to put the returned values in Column B? (It might be a little easier if you can do B, then C, then D, etc. depending on how many results there are). Will it always be three returns per cell, or is that just in your example? – BruceWayne Mar 30 '17 at 14:45
  • it can be any number of returns actually. let me fix the question. I am now thinking of starting from the lookup table and try to substitute all strings in the data sheet. Honestly I am struggling a little with this. I am hoping to find a nifty formula instead of writing a VBA script. – zabumba Mar 30 '17 at 14:49
  • I am thinking that what I want to achieve may not be possible without iterating using VBA – zabumba Mar 30 '17 at 15:17
  • 1
    Can you perhaps use a helper sheet/column(s)? I'm thinking split your data via Text to Columns, after running a `Substitute` formula that will make your Column A into `one, 1; two, 2; three, 3`. Could that maybe work for you? But it may get unweildy if you have say 4 or more substitutes. You may need VBA for this. – BruceWayne Mar 30 '17 at 15:37
  • I wouldn't mind creating separate column and then recompose. I think you are right VBA is probably my best option – zabumba Mar 30 '17 at 15:52
  • VBA is going to be your best bet, any formula will only be able to put the each return in it's on cell so the first row would have three cells, the second 2. This formula would be an array formula and if you have a lot of data in the lookup table it will cause a lag in the calculations. If you do not mind the lag and the values in different cells then it can be done with a formula pretty easily. – Scott Craner Mar 30 '17 at 16:50
  • One note, if you had a subscription to Office 365 excel it would be possible to put the whole return in one cell with a formula. – Scott Craner Mar 30 '17 at 16:51
  • @ScottCraner - Really? If it's not much work, what formula? I'm scratching my head. ...I have a feeling `Aggregate()` may be used, or `TextJoin()`? – BruceWayne Mar 30 '17 at 17:19
  • 1
    @BruceWayne I put the the reference table in K1:L4, then used this in B1: `=TEXTJOIN(",",TRUE,IF(ISNUMBER(SEARCH($K$1:$K$4,A1)),$L$1:$L$4,""))` It is an array formula so Ctrl-Shift-Enter. But it needs Office 365 Excel subscription. The other(per Cell) in B2: `=IFERROR(INDEX($L:$L,AGGREGATE(15,6,ROW($K$1:$K$4)/(ISNUMBER(SEARCH($K$1:$K$4,$A1))),COLUMN(A:A))),"")` drag over and down. – Scott Craner Mar 30 '17 at 17:29
  • 1
    @BruceWayne note, that puts the output in the order of the reference list and not the column A. – Scott Craner Mar 30 '17 at 17:40
  • @ScottCraner - Oh woah, that's great. Thanks for that, I'll have to note it somewhere. – BruceWayne Mar 30 '17 at 17:42
  • 1
    @zabumba see my latest edit to my answer, I have given a link to a UDF and a formula for that UDF that will do what you want. – Scott Craner Mar 30 '17 at 18:26

2 Answers2

2

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

BruceWayne
  • 22,923
  • 15
  • 65
  • 110
  • Sorry, you got me thinking on how to do this, Did not mean to steal your thunder. My formula basically does what your whole process does. – Scott Craner Mar 30 '17 at 17:52
  • 1
    You want something really cool, Sort your reference data on column A and put this in B1 and copy down: `=TEXTJOIN(",",TRUE,IFERROR(LOOKUP(TRIM(SUBSTITUTE(MID(SUBSTITUTE($A1,",",REPT(" ",999)),({1,2,3}-1)*2*999+1,2*999),REPT(" ",999),",")),Sheet2!A:A,Sheet2!B:B),""))` it will put it in one cell in order of the data in A1. – Scott Craner Mar 30 '17 at 18:07
  • @ScottCraner Woah, that's awesome! Thanks a lot for your comments/tips on this one. I've marked it so I can come back in the future if I need to do something like OP. Cheers! – BruceWayne Mar 30 '17 at 18:16
  • 1
    I am not done: This one is variable based on the number of entries in each row: `=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),""))` Again array formula. – Scott Craner Mar 30 '17 at 18:17
2

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

Community
  • 1
  • 1
Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • 1
    [Yesss!](http://gif-finder.com/wp-content/uploads/2015/08/Dwight-YesYes.gif) - I was trying to figure how to use that combination of formulas, but couldn't figure how to make the `Substitute()` part dynamic. If it was *always* three things to return, I was getting there. This had me stumped, hence my rather clunky answer above. I am pleased to give you my thunder on this one, well done! – BruceWayne Mar 30 '17 at 17:57