Extraction of substring

[Kiara12]

Hi, Wishing you a great day .
I am trying to extract a substring from a cell starting from character '!' till the end of the text in that cell and replace it with another text where I want a particular filed in the text to reference its corresponding another cell value.


For instance lets say this is a sample

Now here I want to select the text from ! Till the end of the string in cell B2 and replace it with How123you , that is it should take the value from cell A2 in cell C2. How can we write a formula for this.

How(value incolumn A)you does not come from any of the cells in the Table is just a string that I want to substitute in place of the extracted string from B2. and () should have the value from cell A2 and the result in c2,c3 should look like HelloHow123you
BHow456you

 

[Peter_SSs]

Welcome to the MrExcel board!

See if this helps.

23 05 06.xlsm
	A	B	C
1
2	123	Hello!World	HelloHow123you
3	456	B!right lives	BHow456you
Swap text

Cell Formulas
Range	Formula
C2:C3	C2	=LEFT(B2,FIND("!",B2)-1)&"How"&A2&"you"

 

[mohadin]

Hi
What about another option

=SUBSTITUTE(B2,"!","")&A2&"You"

 

[Kiara12]

Welcome to the MrExcel board!

See if this helps.

23 05 06.xlsm
	A	B	C
1
2	123	Hello!World	HelloHow123you
3	456	B!right lives	BHow456you
Swap text

Cell Formulas
Range	Formula
C2:C3	C2	=LEFT(B2,FIND("!",B2)-1)&"How"&A2&"you"

Thank you

 

[Peter_SSs]

Hi
What about another option

=SUBSTITUTE(B2,"!","")&A2&"You"

Did you try it? It doesn't produce the requested results.

Thank you

You're welcome. Thanks for the follow-up.

 

[mohadin]

@Peter_SSs
Yes I did And It doesn't produce the requested results.!!!!