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]

Maybe:

	A	B
apple	@
An APPLE a day keeps the doctor away
An @apple a day keeps the doctor away
I like AppleJack
I like @appleJack

<tbody>
[TD="align: center"]1[/TD]

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

[TD="align: right"][/TD]

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

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"][/TD]

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

[TD="align: right"][/TD]

</tbody>

Sheet5​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]A3[/TH]
[TD="align: left"]=IFERROR(REPLACE(A2,SEARCH($A$1,A2),1,$B$1&MID(A2,SEARCH($A$1,A2),1)),A2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


Please note that the addition of the @ sign before the word causes the forum software to force the word to lower case. On my test sheet, A3 shows APPLE, and A6 shows AppleJack.

Note that SEARCH is case-insensitive.

 

[Fluff]

you can use UPPER
=FIND(UPPER(J2),UPPER(K2))

 

[bharath235]

hi Eric,

sorry if i did not convey my request clearly .. i want A3 to be resultant of A1 and A2

that is ...

if the value of A1 is present in A2, i want to generate A3 in a particular manner

example:
[TABLE="width: 638"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]River[/TD]
[TD]The river flows east[/TD]
[TD]The <span class="highlight">river</span> flows east[/TD]
[/TR]
[TR]
[TD]fruit[/TD]
[TD]Apple is a FRUIT[/TD]
[TD]Apple is a <span class="highlight">FRUIT</span>[/TD]
[/TR]
[TR]
[TD]DOG[/TD]
[TD]Where is the dog food?[/TD]
[TD]Where is the <span class="highlight">dog</span> food?[/TD]
[/TR]
</tbody>[/TABLE]

the value in A is searched in B and C is the result .. please note that the result is case insensitive .. is this possible?

 

[bharath235]

hi Eric,

sorry if i did not convey my request clearly .. i want A3 to be resultant of A1 and A2

that is ...

if the value of A1 is present in A2, i want to generate A3 in a particular manner

example:
[TABLE="width: 638"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]River[/TD]
[TD]The river flows east[/TD]
[TD]The @river flows east[/TD]
[/TR]
[TR]
[TD]fruit[/TD]
[TD]Apple is a FRUIT[/TD]
[TD]Apple is a @fruit[/TD]
[/TR]
[TR]
[TD]DOG[/TD]
[TD]Where is the dog food?[/TD]
[TD]Where is the @dog food?[/TD]
[/TR]
</tbody>[/TABLE]

the value in A is searched in B and C is the result .. please note that the result is case insensitive .. is this possible?

 

[Rick Rothstein]

Give this a try...

=IFERROR(REPLACE(B1,SEARCH(A1,B1),0,"@"),B1)

 

[bharath235]

hi eric,

sorry .. what i intend is as follows

What i am trying to do is:
a. search if the value of a is in B
b. if yes, add a character (in this case @) before that particular value, however, retaining the case and style of B as is

[TABLE="class: cms_table, width: 638"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]River[/TD]
[TD]The river flows east[/TD]
[TD]The @river flows east[/TD]
[/TR]
[TR]
[TD]fruit[/TD]
[TD]Apple is a FRUIT[/TD]
[TD]Apple is a @FRUIT[/TD]
[/TR]
[TR]
[TD]DOG[/TD]
[TD]Where is the dog food?[/TD]
[TD]Where is the @dog food?




[/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

The same basic formula works, you just have to change the ranges to match your sheet:

	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?

<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%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="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),1,"@"&MID(B1,SEARCH(A1,B1),1)),B1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


I added a space before FRUIT in C2 so that the case remains the same. The space won't appear in your real sheet.

 

[bharath235]

hi rick .. thanks for your assistance .. that works perfectly .. i am trying to add a html span class tag to the value .. so there would be before and after strings .. can the below case be achieved? thx a ton for your help

What i am trying to do is:
a. search if the value of a is in B
b. if yes, add a character (in this case @) before and after that particular value, however, retaining the case and style of B as is

[TABLE="class: cms_table_cms_table, width: 638"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]River[/TD]
[TD]The river flows east[/TD]
[TD]The @river@ flows east[/TD]
[/TR]
[TR]
[TD]fruit[/TD]
[TD]Apple is a FRUIT[/TD]
[TD]Apple is a @FRUIT@[/TD]
[/TR]
[TR]
[TD]DOG[/TD]
[TD]Where is the dog food?[/TD]
[TD]Where is the @dog@ food?



[/TD]
[/TR]
</tbody>[/TABLE]

 

[bharath235]

hi eric .. i am trying to add a html tag .. sorry i meant to place @ as a placeholder as the html inputs where not showing up here .. is it possible to produce @VALUE@ .. that is @ before and after .. thx for your help