Help!. Extraction of substring

[Kiara]

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

 

[vletm]

Kiara
You sample picture's cell A2 has 123.
You want to use it as How123you. Where comes those How and you?
What would be show in cell C2? ... Hello!How123you or what?

 

[Kiara]

How(cell number)you is a common string that I want to replace !World with . So cell C2 will be HelloHow(123)you. Herethe cellnumber in between how and you should be taken from A1. and How(cellnumber)you will be same for the entire column C

 

[vletm]

Kiara
Your sample picture's cell B2 has Hello!Word.
As my the 1st question: Where comes those How and you?
Where comes ( & ) ?
Your sample picture's cell A1 looks ... empty.

 

[Kiara]

How()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

 

[p45cal]

=LEFT(B2,FIND("!",B2)-1) & "How" & A2 & "You"
?

=LEFT(B2,FIND("!",B2)-1) & "How" & $A$1 & "You"
?