VBA to split names

[the magician]

Hi gang. Got a list of names I'd like to split out the last name using VBA not a cell formula. Could be such as:
John Smith
John Smith Jr
Mary and Bill Jones
Ed & Edna Brown
Irene Cole-Dunn

So I'm looking for that space before last name to split it into two columns. And that "Jr" is a distinct possibility, as much trouble as it might be.

Thanks,
el mago

 

[Sthrncali]

This worked for me. This will only split the name at the first space it finds. So if there is a Don Paul Jr. it would result in Paul Jr. as the last name.

Sub SplitNames()
Dim FName As Integer, WholeName As Integer
Dim FNameCol As Integer, LNameCol As Integer
'Set FNameCol to the Column Number you want the scrubbed first name saved to.
FNameCol = 2
'Set LNameCol to the Column Number you want the scrubbed Last name saved to.
LNameCol = 3
''Change ("A100000") to the column where your whole name is saved.
For r = 1 To ActiveSheet.Range("A100000").End(xlUp).Row
'Change Activesheet.cells(r,1) to the correct column number for your combined name list. ColA = 1, ColB = 2, Etc.
FName = Application.WorksheetFunction.Find(" ", ActiveSheet.Cells(r, 1).Value, 1)
WholeName = Len(ActiveSheet.Cells(r, 1).Value)
ActiveSheet.Cells(r, FNameCol).Value = Left(ActiveSheet.Cells(r, 1).Value, FName)
ActiveSheet.Cells(r, LNameCol).Value = Right(ActiveSheet.Cells(r, 1).Value, WholeName - FName)
Next
End Sub

 

[the magician]

This worked for me. This will only split the name at the first space it finds. So if there is a Don Paul Jr. it would result in Paul Jr. as the last name.

Thanks for the code. It's a start, although I really need to work backwards on this and get the last space, not the first. I'll deal with Junior.

Also I had this problem: on the "Fname =" line I got the error message "Unable to get the Find property of the WorksheetFunction class". And yet, it seems to be available.

I apologize for not including I'm on Win7 with Excel 2010, should that help (as it often does).

Thanks.