=IFERROR(LEFT(B9,FIND(" ",B6)),B6)

So currently this returns the first word found in a cells string.

It works as intended on that part but for example if it returned Apple in the cell I wish to paste it in.

How would I add my text alongside it?

For example, I want it to paste My Apple and not just the value found which would just be Apple.

CodePudding user response：

If you want add extra word then just concatenate it either by CONCATENATE() formula or concatenate operator &. Try-

="My " & IFERROR(LEFT(B9,FIND(" ",B6)),B6)