Substitute string in cell if one of many

[Okoth]

I am totally stuck with this. I have tried helper cells, substitute and sumif formulas but I cannot get what I want.

So, I have a list with values. There is a text and if a word in the text matches a word in the list then it has to be replaced with a word that is in a cell next to the cell in the list.

An example

[TABLE="class: grid, width: 600, align: left"]
<tbody>[TR]
[TD]TEXT[/TD]
[TD]LIST[/TD]
[TD]becomes[/TD]
[TD]THIS[/TD]
[/TR]
[TR]
[TD]The white cat will be replaced by a black fish[/TD]
[TD]duck[/TD]
[TD]becomes[/TD]
[TD]mouse[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]cat[/TD]
[TD]becomes[/TD]
[TD]fish[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]dog[/TD]
[TD]becomes[/TD]
[TD]bird[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]sheep[/TD]
[TD]becomes[/TD]
[TD]rat[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]monkey[/TD]
[TD]becomes[/TD]
[TD]owl[/TD]
[/TR]
[TR]
[TD]Result:[/TD]
[TD]worm[/TD]
[TD]becomes[/TD]
[TD]eagle[/TD]
[/TR]
[TR]
[TD]The white fish will be replaced by a black fish[/TD]
[TD]goose[/TD]
[TD]becomes[/TD]
[TD]mole[/TD]
[/TR]
</tbody>[/TABLE]

How can I do this?

 

[admiral100]

Hi,

Try this :

A7 =SUBSTITUTE(A1,INDEX(B1:B7,SUMPRODUCT(--ISNUMBER(SEARCH(CONCATENATE("*",B1:B7,"*"),CONCATENATE("*",SUBSTITUTE(A1," ","*"),"*")))*ROW(B1:B7))),INDEX(D1:D7,SUMPRODUCT(--ISNUMBER(SEARCH(CONCATENATE("*",B1:B7,"*"),CONCATENATE("*",SUBSTITUTE(A1," ","*"),"*")))*ROW(B1:B7))))

[TABLE="width: 580"]
<colgroup><col><col><col span="3"></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]The white cat will be replaced by a black fish[/TD]
[TD]duck[/TD]
[TD]becomes[/TD]
[TD]mouse[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]cat[/TD]
[TD]becomes[/TD]
[TD]fish[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]dog[/TD]
[TD]becomes[/TD]
[TD]bird[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]sheep[/TD]
[TD]becomes[/TD]
[TD]rat[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]monkey[/TD]
[TD]becomes[/TD]
[TD]owl[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Result:[/TD]
[TD]worm[/TD]
[TD]becomes[/TD]
[TD]eagle[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]The white fish will be replaced by a black fish[/TD]
[TD]goose[/TD]
[TD]becomes[/TD]
[TD]mole[/TD]
[/TR]
</tbody>[/TABLE]

 

[Okoth]

Thank you. The example worked. Going to try and get it working in my work sheet.

 

[Okoth]

I found out that it the solution doesn't work when the list cells contain more than one word. Is there a way to adjust the solution above?

Sorry for the double work.

 

[admiral100]

If there are more than 2 words I would suggest to considering a VBA solution...

Ctrl+Shift+Enter NOT just Enter

A7 =SUBSTITUTE(SUBSTITUTE(A1,INDEX(B1:B7,SMALL(LARGE(--ISNUMBER(SEARCH(CONCATENATE("*",B1:B7,"*"),CONCATENATE("*",SUBSTITUTE($A$1," ","*"),"*")))*ROW(B1:B7),{1,2}),{1,2})),INDEX(D1:D7,SMALL(LARGE(--ISNUMBER(SEARCH(CONCATENATE("*",B1:B7,"*"),CONCATENATE("*",SUBSTITUTE($A$1," ","*"),"*")))*ROW(B1:B7),{1,2}),{1,2}))),INDEX(B1:B7,LARGE(--ISNUMBER(SEARCH(CONCATENATE("*",B1:B7,"*"),CONCATENATE("*",SUBSTITUTE($A$1," ","*"),"*")))*ROW(B1:B7),{1,2})),INDEX(D1:D7,LARGE(--ISNUMBER(SEARCH(CONCATENATE("*",B1:B7,"*"),CONCATENATE("*",SUBSTITUTE($A$1," ","*"),"*")))*ROW(B1:B7),{1,2})))


[TABLE="width: 799"]
<colgroup><col><col><col span="3"></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]The white cat and the black sheep will be replaced by a black fish and white rat[/TD]
[TD]duck[/TD]
[TD]becomes[/TD]
[TD]mouse[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]cat[/TD]
[TD]becomes[/TD]
[TD]fish[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]dog[/TD]
[TD]becomes[/TD]
[TD]bird[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]sheep[/TD]
[TD]becomes[/TD]
[TD]rat[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]monkey[/TD]
[TD]becomes[/TD]
[TD]owl[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Result:[/TD]
[TD]worm[/TD]
[TD]becomes[/TD]
[TD]eagle[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]The white fish and the black rat will be replaced by a black fish and white rat[/TD]
[TD]goose[/TD]
[TD]becomes[/TD]
[TD]mole[/TD]
[/TR]
</tbody>[/TABLE]

 

[Okoth]

Sorry, I think I wasn't clear enough. I meant in the B column a cell could be having more than one word.

[TABLE="width: 691"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]The white hairy dog will be replaced by a black fish[/TD]
[TD]duck[/TD]
[TD]becomes[/TD]
[TD]mouse[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]cat[/TD]
[TD]becomes[/TD]
[TD]fish[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]hairy dog[/TD]
[TD]becomes[/TD]
[TD]bird[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]sheep[/TD]
[TD]becomes[/TD]
[TD]rat[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Result:[/TD]
[TD]monkey[/TD]
[TD]becomes[/TD]
[TD]owl[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]The white bird will be replaced by a black fish[/TD]
[TD]worm[/TD]
[TD]becomes[/TD]
[TD]eagle[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD]goose[/TD]
[TD]becomes[/TD]
[TD]mole[/TD]
[/TR]
</tbody>[/TABLE]

 

[admiral100]

A7 =SUBSTITUTE(A1,INDEX(B1:B7,SUMPRODUCT(--ISNUMBER(SEARCH(SUBSTITUTE(CONCATENATE("*",B1:B7,"*")," ","*"),CONCATENATE("*",SUBSTITUTE(A1," ","*"),"*")))*ROW(B1:B7))),INDEX(D1:D7,SUMPRODUCT(--ISNUMBER(SEARCH(SUBSTITUTE(CONCATENATE("*",B1:B7,"*")," ","*"),CONCATENATE("*",SUBSTITUTE(A1," ","*"),"*")))*ROW(B1:B7))))

[TABLE="width: 619"]
<colgroup><col><col><col span="3"></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]The white hairy dog will be replaced by a black fish[/TD]
[TD]duck[/TD]
[TD]becomes[/TD]
[TD]mouse[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]cat[/TD]
[TD]becomes[/TD]
[TD]fish[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]hairy dog[/TD]
[TD]becomes[/TD]
[TD]bird[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]sheep[/TD]
[TD]becomes[/TD]
[TD]rat[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]monkey[/TD]
[TD]becomes[/TD]
[TD]owl[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Result:[/TD]
[TD]worm[/TD]
[TD]becomes[/TD]
[TD]eagle[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]The white bird will be replaced by a black fish[/TD]
[TD]goose[/TD]
[TD]becomes[/TD]
[TD]mole[/TD]
[/TR]
</tbody>[/TABLE]

 

[Okoth]

That's really amazing. Wish I knew as much about Excel as you. Thank you very much. I really appreciate all the time you have put into it!

 

[admiral100]

You are welcome

 

[jtakw]

Hi,

I have a much simpler solution if you're still interested.