Use the =SPLIT() function to separate the words (sort of like Text to columns):

Sub oliver()
v = ""
For Each r In Selection
s = Split(r.Value, " ")
If UBound(s) 0 Then
r.Value = s(0)
r.Offset(0, 1).Value = s(1)
End If
Next
End Sub
--
Gary''s Student - gsnu200728


"Oliver St Quintin" wrote:

How do I program a macro to delete the first (or second, or third) text word
in a series of cells?

Specific application is that I want to convert a list of names from:
Joe Smith (in one cell) to
Joe (one cell) and Smith (one cell)

When I use the macro recorder to record my manual actions to delete Joe from
Joe Smith, it records the result of the actions rather than the actions
themselves.

For example, my manual actions to do this we
F2, home, ctrl-R, delete, enter

but the recorded macro is:
ActiveCell.FormulaR1C1 = "Smith"
Range("A5").Select

which when executed on another cell with a different name like Tom Brown,
changes the text to Smith.

This was very easy to do in Lotus 123, but I'm at a loss as to how to do it
in Excel.

Thanks

Oliver