Thanks again. I should have thought of that. I'll add that at the beginning
of the macro and give it another shot.

"Gary''s Student" wrote:

I was able to duplicate your problem with the "Alou" record.

There is an extra space after NYM.

You can use the TRIM() function in the worksheet to remove these little pests.
--
Gary''s Student - gsnu200774


"Phil Trumpy" wrote:

Thank you very much. This works almost perfectly. However, there seem to be
a few records that did not get extracted correctly. Also, I did not account
for a LastName that could have a space in it (De Aza in this case). Here is
an example of one of the records that didn't seem to work:

Before
Col A
Alou, Moises RF NYM

After
Col A Col B Col C Col D
Alou Moises RF NYM

Any ideas?




"Gary''s Student" wrote:

This macro assumes that the fields are separated by a single space. The
macro does the equivalent to Text-to-Columns. It splits the data up into
either 5 or 4 parts.

If there are only four parts, then the middle initial is missing and the
data is placed into positions 1,2,4,5.

If there are five parts, then the middle iniial is present and the data is
placed into positions 1,2,3,4,5:


Sub SplitUm()
Sheets("Sheet1").Activate
Set w2 = Sheets("Sheet2")
n = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To n
s = Split(Cells(i, 1).Value)
u = UBound(s) + 1
w2.Cells(i, 1).Value = Replace(s(0), ",", "")
w2.Cells(i, 2).Value = s(1)
w2.Cells(i, 5) = s(u - 1)
w2.Cells(i, 4) = s(u - 2)
If u = 5 Then
w2.Cells(i, 3) = s(2)
End If
Next
End Sub

--
Gary''s Student - gsnu200774


"Phil Trumpy" wrote:

I'm using Excel 2007 - Data is in column A

I have a data set that I am trying to import. The first column really
should be 5 separate columns, but has been created as one string. I need to
separate into 5 columns. the data looks like this:

LastName, FirstName MI. Position Team

LastName and FirstName can be ulimited characters
MI can be 1 or 2 characters followed by a period
Position is 1 or 2 characters
Team is 3 characters

I attempted to use Text to Columns, but the problem is that the middile
initial does not always exist. If I can get the middle initial to a separate
column, I can use text to columns for the rest. I would prefer to just run
one macro to take care of the whole thing since I will have a different file
each year that has the same data structure. Sorry if I didn't provide enough
info. Thanks in advance.