Parsing names from one column into 3.

[Kevin Porter]

Have several spreadsheets that have the name of people in one column in the
format - lastname, firstname MiddleInitial.

What I need to do is read that info and parse out each piece of info into
it's own column. One thing I am concerned with is some of the names do not
have middle initials.

Thanks,

Kevin Porter

 

[Tom Ogilvy]

You can get most of the solution by selecting the column of names and doing
Data=>Text to columns and selecting delimited. then select comma as the
delimiter

this should put last names in the original column and everything else in the
adjacent column. Repeat on the adjacent column, but choose space as the
delimiter.

clean up anomalies manually.

 

[Gary''s Student]

Sub qwerty()
For Each r In Selection
s = Split(r.Value, " ")
r.Offset(0, 1).Value = Left(s(0), Len(s(0)) - 1)
r.Offset(0, 2).Value = s(1)
If UBound(s) = 2 Then
r.Offset(0, 3).Value = s(2)
End If
Next
End Sub

 

[Ron Rosenfeld]

Have several spreadsheets that have the name of people in one column in the
format - lastname, firstname MiddleInitial.

What I need to do is read that info and parse out each piece of info into
it's own column. One thing I am concerned with is some of the names do not
have middle initials.

Thanks,

Kevin Porter

Data/Text to Columns
Delimited
Select Space and also Select Comma
Select "Treat consecutive delimiters as one
Finish

Given your format above, it should parse correctly even with NMI.

However, if you have multiple last names separated by a space (prior to the
comma), then do the Text-to-Columns in two steps. For step 1, select only the
comma as a delimiter.

For step 2, select column 2, and then use space as a delimiter.


--ron

 

[Rick Rothstein \(MVP - VB\)]

What about this modification to your subroutine?

Sub qwerty()
For Each R In Selection
S = Split(Replace(R.Value, ",", ""), " ")
For X = 0 To UBound(S)
R.Offset(0, X + 1).Value = S(X)
Next
Next
End Sub

Rick

 

[Gary''s Student]

A very nice improvement !