Replace all text before a specific word in cell B1 with text string in cell A1?

[john deer]

Any Excel Guru's out there that could provide me with a formula? I would like to replace all the text before the word "for" in cell B1 with the text string in A1. Cell A1 has proper casing and an added space after "Handle".

 

[Scott Huish]

If the text before the for is exactly the same in each line as in your example:

=SUBSTITUTE(B1,LEFT(B1,FIND(" for ",B1)-2),A1)

Set the result cell to Wrap Text.

 

[john deer]

if the text before the for is exactly the same in each line as in your example:

=substitute(b1,left(b1,find(" for ",b1)-2),a1)

set the result cell to wrap text.

unbelievable!! It works, thank you so much!