Thanks again to everyone for your response. I have solved the problem in the
following way using David McRitchie's suggestion.

To recap the issue (after all there was a lot of back and forth and not
really in order)the following name list was to be separated into three
separate columns (first name, middle initial, last name).

Joe T. Schmoe
Karen K. Canterberry
Simone Karter
Lois-Martin Fender

I created a macro with the following text:

Sub SepLastTerm()
'David McRitchie 1998-08-20 [Ctrl+l] documented in
' http://www.mvps.org/dmcritchie/excel/join.htm
'Separate the last term from remainder, as in separating
'lastname from firstname
'Work on first column, cell to right must appear to be blank
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual 'pre XL97 xlAutomatic
'On Error Resume Next
Dim iRows As Long, mRow As Long, ir As Long
iRows = Selection.Rows.Count
Set lastcell = Cells.SpecialCells(xlLastCell)
mRow = lastcell.Row
If mRow < iRows Then iRows = mRow 'not best but better than nothing
For ir = 1 To iRows
If Len(Trim(Selection.Item(ir, 1).Offset(0, 1))) < 0 Then
iAnswer = MsgBox("Found non-blank in adjacent column -- " _
& Selection.Item(ir, 1).Offset(0, 1) & " -- in " & _
Selection.Item(ir, 1).Offset(0, 1).AddressLocal(0, 0) & _
Chr(10) & "Press OK to process those than can be split", _
vbOKCancel)
If iAnswer = vbOK Then GoTo DoAnyWay
GoTo terminated
End If
Next ir
DoAnyWay:
For ir = 1 To iRows
If Len(Trim(Selection.Item(ir, 1).Offset(0, 1))) < 0 _
Then GoTo nextrow
checkx = Trim(Selection.Item(ir, 1))
L = Len(Trim(Selection.Item(ir, 1)))
If L < 3 Then GoTo nextrow
'-- this is where SepLastTerm differs from SepTerm
For im = L - 1 To 2 Step -1
If Mid(checkx, im, 1) = " " Then
Selection.Item(ir, 1) = Left(checkx, im - 1)
Selection.Item(ir, 1).Offset(0, 1) = Trim(Mid(checkx, im + 1))
GoTo nextrow
End If
Next im
nextrow:
Next ir
terminated:
Application.Calculation = xlCalculationAutomatic 'pre XL97 xlAutomatic
Application.ScreenUpdating = True
End Sub

This macro took the last name and inserted it into a separate column. Then
I was left with the first name and sometimes a middle initial in one column.
I separated that one using the "text to column" feature.

Gord, I realized that it was David's macro that I used and that I got to
work. Sorry, but many thanks for you help as well.

Now, after all this, I have to jump over to the Word section because I need
to do exactly the same thing just in Word. I copied the table from Word into
Excel thinking that I could sort it better there, but did not realize that
there are now merged cells and copying it back into Word gave me trouble. Oh
may....

"kikilein" wrote:

I have a column of names first name, middle initial (sometimes), last name.

I have a hard time to separate that middle initial that some of the names
have but not all. I tried the Data text to column but have a hard time
seperating the middle intitial. Any ideas on how to do it?

Thanks much.