Parsing names from one column into 3.
 

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

Parsing names from one column into 3.
 

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.

--
Regards,
Tom Ogilvy


"Kevin Porter" wrote:

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

Parsing names from one column into 3.
 

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

--
Gary''s Student - gsnu200739


"Kevin Porter" wrote:

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

Parsing names from one column into 3.
 

On Fri, 24 Aug 2007 09:14:02 -0700, Kevin Porter
wrote:

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

Parsing names from one column into 3.
 

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" wrote in message
...
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

--
Gary''s Student - gsnu200739


"Kevin Porter" wrote:

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

Parsing names from one column into 3.
 

A very nice improvement !
--
Gary''s Student - gsnu200739


"Rick Rothstein (MVP - VB)" wrote:

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" wrote in message
...
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

--
Gary''s Student - gsnu200739


"Kevin Porter" wrote:

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