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?

 

[Okoth]

Hi,

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

Yes, please. I still might have problems with the code above with the data I have, but still testing...

 

[jtakw]

Hi,

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

Yes, please. I still might have problems with the code above with the data I have, but still testing...

This will work for any number of words for Column B to be replaced/substituted in the subject text string in A1 by corresponding word(s) in Column D:

Book1
	A	B	C	D
1	The bald polar bear cub will be replaced by a black fish	duck	becomes	mouse
2		cat	becomes	fish
3		hairy dog	becomes	bird
4		sheep	becomes	rat
5		monkey	becomes	owl
6	Result:	worm	becomes	eagle
7	The giant brown bear will be replaced by a black fish	bald polar bear cub	becomes	giant brown bear
8	The giant brown bear will be replaced by a black fish
Sheet120

Cell Formulas
Range	Formula
A7	=SUBSTITUTE(A1,LOOKUP(2,1/FIND($B$1:$B$7,A1),$B$1:B$7),LOOKUP(2,1/FIND($B$1:$B$7,A1),$D$1:$D$7))
A8	=SUBSTITUTE(A1,LOOKUP(2,1/SEARCH($B$1:$B$7,A1),$B$1:B$7),LOOKUP(2,1/SEARCH($B$1:$B$7,A1),$D$1:$D$7))

A7 formula case sensitive.
A8 formula Not case sensitive.

 

[Okoth]

Hi,

I tried the A8 formula and it gave me immediately what I wanted. Thanks.

 

[jtakw]

You're welcome, happy it works for you.

Edit: Also, your Column B and D range can be as long as you need, just adjust the ranges in the formula to suit.