Take first word out of cell and put it in a new cell

[LM]

I have a list of data and I want to remove the first word of each cell in one
column and put it in a separate column. Is there a formula for this?

 

[Bob Phillips]

=LEFT(A1,FIND(" ",A1)-1)

 

[LM]

Thanks Bob, but that only copies the word. I want to cut it out of the cell
rather than copy it. Any other suggestions?

Lyn.

 

[Bob Phillips]

You can't do that with a formula, you would need VBA, macro code.

 

[Dave Peterson]

Can you use two formula cells?

Use Bob's formula to get the first word.

Then use this formula to get the rest of the words.
=MID(A1,FIND(" ",A1)+1,LEN(A1))

If you wanted, you could convert both these columns of formulas to values
(edit|copy, edit|Paste special|values) and delete the original data.

 

[LM]

Just brilliant. That worked a treat. You have just saved me days of work -
I thought I would have to do it manually. Thank you so much to both of you.