macro to split names people part 2

route217 used Ask the Experts™
Question: Hi Experts

Need a macro to split the first name and surnames of people according to the following rules:

1. If column A2 onwards has a person name as Joe Mark Robinson and a blank cell in column B2 so you would see the following in a excel worksheet

Column A2                           Column B2
Joe Mark Robinson               empty cell (THEN DO THE FOLLOWING)

Joe Mark                              Robinson (surname only)

(first name & Middle name or initial) - if no middle name then or initial then first name ONLY

The opposite to the folllowing macro:-
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010
This time select the cells in Col A you want to process and run:

Sub SplitNames()
    Dim cel As Range
    For Each cel In Selection.Cells
        If InStrRev(cel, " ") > 0 And cel.Offset(0, 1) = "" Then
            cel.Offset(0, 1) = Mid(cel, InStrRev(cel, " ") + 1)
            cel = Left(cel, InStrRev(cel, " ") - 1)
        End If
End Sub
Ardhendu SarangiSr. Project Manager
Try this...
- Ardhendu
Option Explicit

Sub lastname()
    Dim i As Integer, spcCtr As Integer, j As Integer, loc As Integer

    For i = 2 To Cells(65536, "A").End(xlUp).Row
        If Range("B" & i) = "" Then
            spcCtr = 0
            For j = 1 To Len(Range("A" & i))
                If Mid(Range("A" & i), j, 1) = " " Then
                    spcCtr = spcCtr + 1
                    If j <> Len(Range("A" & i)) Then
                        loc = j
                    End If
                End If
            Range("B" & i) = Mid(Range("A" & i), loc + 1, Len(Range("A" & i)))
            Range("A" & i) = Left(Range("A" & i), loc - 1)
        End If
End Sub

Open in new window



thanks for the feedback.............excellent

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial