Macro to Delete Partial Text in a Cell

[Guest]

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 were:
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

 

[RB Smissaert]

This is something where the macro recorder won't help you much.
You need something like this:

Sub SplitNames()

Dim i As Long
Dim LR As Long
Dim arr1
Dim arr2
Dim arr3

LR = Cells(65536, 1).End(xlUp).Row

arr1 = Range(Cells(1), Cells(LR, 1))

ReDim arr3(1 To LR, 1 To 2)

For i = 1 To LR
arr2 = Split(arr1(i, 1), " ")
arr3(i, 1) = arr2(0)
arr3(i, 2) = arr2(1)
Next i

Range(Cells(3), Cells(LR, 4)) = arr3

End Sub

The full names should be in column A and the new data will be dumped in
columns C and D.
You may need to add some more code to deal with names that are not like
firstname space surname.


RBS

 

[Guest]

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