find a particular value in a cell and add a string to it (case insensitive)

[bharath235]

in one cell (say A1) I have a word, example - apple


in the next cell (say A2) I have a sentence, example - An APPLE a day keeps the doctor away


is it possible to render the following result in another cell (say A3)? - An @APPLE a day keeps the doctor away

I have introduced a character before the word "apple" ...


please note that I tried search, find, substitute, and replace, the real problem I am facing is that the text in A1 and A2 can be of different cases and are absolute variables ... can a vba function be written to solve this?


Thanks for your help!

 

[Eric W]

Sure, replace the {} in the formula with your actual symbols:

	A	B	C
River	The river flows east	The {river} flows east
fruit	Apple is a FRUIT	Apple is a {FRUIT}
DOG	Where is the dog food?	Where is the {dog} food?

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

</tbody>

Sheet5​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C1[/TH]
[TD="align: left"]=IFERROR(REPLACE(B1,SEARCH(A1,B1),LEN(A1),"{"&MID(B1,SEARCH(A1,B1),LEN(A1))&"}"),B1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[bharath235]

thanks very much, Eric .. it works perfect ..

 

[Rick Rothstein]

thanks very much, Eric .. it works perfect ..

Here is my formula modified to do what you are now asking for...

=IFERROR(REPLACE(REPLACE(B1,SEARCH(A1,B1),0,"{"),SEARCH(A1,B1)+LEN(A1)+1,0,"}"),B1)