macro to split name(s) people data

route217
route217 used Ask the Experts™
on
Hi Experts

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

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

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

                              Robinson (surname only)
Joe Mark
(first name & Middle name or initial) - if no middle name then or initial then first name ONLY

so i want the macro to look down column B and split the name according to the above example.

If cell A2 onwards is empty look in corresponding cell B2 if a name is present split the name according to example shown.
                                     





 
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010

Commented:
Hello route217,

It would be useful to see some sample data and/or a sample file.  EE now allows you to directly upload files
to your question.

Please be advised that once you upload a file, it can be publicly accessed, and that it may not be possible
to fully and permanently delete it.  Therefore, be very careful about posting proprietary, confidential, or
other sensitive information.  If necessary, use "fake" and/or obfuscated data in your sample.

Please note that at present EE restricts uploads to certain file types.  If your file type does not match
those in the list, you can use http://www.ee-stuff.com instead, which is not officially an EE site, but is run
by people connected to EE.

Regards,

Patrick
route217Junior

Author

Commented:
Hi patrick,

Will provide dummies workbook
route217Junior

Author

Commented:
file attached with made up data
sample.xls
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

Sr. Project Manager
Commented:
Hi.

Try the following code -

See attached file,

-Ardhendu
Option Explicit

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


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

Open in new window

sample1.xls
Top Expert 2010
Commented:
route217,

This code worked on your sample data.  Select B2:B7, and then execute this:


Sub SplitNames()
   
    Dim cel As Range
   
    For Each cel In Selection.Cells
        If InStrRev(cel, " ") > 0 Then
            cel.Offset(0, -1) = Left(cel, InStrRev(cel, " ") - 1)
            cel = Mid(cel, InStrRev(cel, " ") + 1)
        End If
    Next
   
End Sub



Patrick
route217Junior

Author

Commented:
hi pari

does yur code post the final data in a new worksheet????? if so can you amend to remain in same worksheet.

Patrick thanks for the feedback....excellent
route217Junior

Author

Commented:
part two here the exact opposite to this question...........column A2 with names and column B2 onwards empty

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_25095362.html

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