separating text from cells

  • Thread starter Thread starter Guest
  • Start date Start date
Great to hear!!!!

Gord

YES!!!! GOT IT!!!! Thanks so much.

I used the macro and it extracted last name. Then I did a text to "Text to
column" with the remaining firstname and middle initial cell seperating them
.

I have them now all how I wanted them.

Thanks you all for all you brilliant suggestions.
Click to expand...

Gord Dibben MS Excel MVP
 
Thank you all so much for all your replies. I will try one by one to see if
something works for me (I am not so good when it comes to VBA and Macros :-)
but will venture this out). I will report back.

BTW: the names are listed as follows: first_name middle_initial. last_name
(althought the middle initial is missing in some of the names).
Click to expand...

Then, after downloading and installing morefunc.xll as I posted previously,
just use these formulas:

First Name: =REGEX.MID(A2,"^\w+(?=\s)")
Middle Initial: =REGEX.MID(A2,"(?<=\s)\w\.?(?=\s)")
Last Name: =REGEX.MID(A2,"\w+$")


--ron
 
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....
 
Back
Top