Text (cell value) text

[afountas]

I need a formula that will insert a cell value inside a sentence.

IE

If in one cell I have the text "The Red Apple"

& in cell A2 I have the text "Yellow"

Need a Formula that will say "The (A2) Apple"

and the result should be

"The Yellow Apple"

 

[VoG]

Perhaps

=LEFT(A1,FIND(" ",A1))&A2&" "&TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),255))