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