parsing full names in to 3 columns
Oops. I didn't ready your initial request carefully enough. I didn't
notice that you wanted the names put into 3 columns.
Here's some simple code I put together that will put the names into 3
columns. It does rely on the names being in contiguous rows. If your
column is not in row A, just change the line that says Set myvar =
Range("A2"). Also, note that I'm starting on the 2nd row assuming
you'll have a title row on row 1.
Sub FirstToLast()
Dim myvar As Variant
Dim lkforComma As Integer
Dim lkforSpace As Integer
Dim fName, mName, lName As String
Set myvar = Range("A2")
Do While Not IsEmpty(myvar)
Set nextvar = myvar.Offset(1, 0)
Set firstName = myvar.Offset(0, 1)
Set middleName = myvar.Offset(0, 2)
Set lastName = myvar.Offset(0, 3)
lkforComma = InStr(myvar, ",")
lName = Left(myvar, lkforComma - 1)
fName = Mid(myvar, lkforComma + 2)
lkforSpace = InStr(fName, Chr(32))
mName = Mid(fName, lkforSpace + 1)
fName = Mid(fName, 1, lkforSpace - 1)
firstName.Value = fName
middleName.Value = mName
lastName.Value = lName
Set myvar = nextvar
Loop
End Sub
|