Separate first and last name in two columns

[Greg Snidow]

Greetings all. I need to create a last name column and a first name column
from an existing column that contains lastname,firstname m, where the "m"
denotes a middle initial. For example, the first row might me "Smith,Joe"
and the second row might be "Doe,Jane M". The problem is that some of the
names do not contain a middle initial. I got a solution to work, but it has
a bunch of loops, and takes about 15 seconds to run on 3500 rows. I'm
looking for a more efficient way of pulling out the last and first name,
ignoring the first initial. I was looking for an equivalent to SQL Server's
CHARINDEX(), but no luck I guess. Below is the code, and I would welcome any
suggestions to make it better. Thank you.

Greg

'Start in row 2
'Column "C" contains a name field with format Lastname,firstname m
'Column "A" will get the last name, and "B" will get first name
'Only looking at first 10 rows for now
For i = 2 To 11 Step 1
For c = 1 To 10 Step 1
'Look for the comma
If Mid(Range("C" & i), c, 1) = "," Then
'Populate the last name in column "A", everything up to the
","
Range("A" & i).Value = Left(Range("C" & i), c - 1)

'Need a variable to be the default Len of the name
'Start by making it the length of the name, including
initial if present
z = Len(Range("C" & i))
'Now, starting in the first position after the comma
separating
'first and last name, look to see if each character
position is a blank.
'If it is a blank, set z = to its location.
'For names where there is no space and first initial
after the first name,
'z should stay the same. If there is a space followed
by the first initial,
'z will become the character position of the space
For b = c + 1 To Len(Range("C" & i)) + 1 Step 1
If Mid(Range("C" & i), b, 1) = " " Then
z = b - 1
End If
Next b
'Now, use the value of z to get just the first name portion
of the name field,
'which is everything starting in the first position after
the comma, up to
'the first blank space, regardless of whether there is a
middle initial after it.
Range("B" & i).Value = Mid(Range("C" & i), c + 1, z - c)
End If
Next c
Next i

 

[Rick Rothstein]

This code should do what you want...

Dim X As Long, LastRow As Long, Names() As String
Const StartRow = 2, NameCol = "C", LastNameCol = "A", FirstNameCol = "B"
LastRow = Cells(Rows.Count, NameCol).End(xlUp).Row
For X = StartRow To LastRow
Names = Split(Cells(X, NameCol).Value, ",")
Cells(X, LastNameCol).Value = Names(0)
If Mid(Names(1), Len(Names(1)) - 1, 1) = " " Then
Cells(X, FirstNameCol).Value = Left(Names(1), Len(Names(1)) - 2)
Else
Cells(X, FirstNameCol).Value = Names(1)
End If
Next

 

[Rick Rothstein]

This code should run **much** faster...

Dim LastRow As Long
LastRow = Cells(Rows.Count, "C").End(xlUp).Row
Range("C2:C" & LastRow).TextToColumns Destination:=Range("A2"), _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, Tab:=False, Semicolon:=False, _
Comma:=False, Space:=True, Other:=True, OtherChar:=",", _
FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 9)), _
TrailingMinusNumbers:=True

Note: You should copy/paste this code so you do mess up the line
continuations (the lines starting with "Range" up to the last line are
actually all one statement... the space/underbar at the end of each line is
the line continuation character sequence and it tells VB to link all the
lines together into a single statement).

 

[Greg Snidow]

Thanks to both for the suggestion. And you are correct Rick, that baby
screams down the column.