RE: separating text from cells

[southwood]

If all names had a middle initial would that solve your problem? If so can
you use a number or character when there is no middle initial. A null
character or blank could work??

"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.

[MartinW]

Hi kikilein,

Text to columns should work. How are the names delimited? With
comma's, spaces, semi-colons? Maybe a mix of all three?
Make sure you select all the possibles on the second page
of the wizard. The screen at the bottom will give you
a preview of how it will look.

HTH
Martin

[Gord Dibben]

Chip Pearson's site deals with this nicely.

Provides a downloadable workbook with examples if you want it.

http://www.cpearson.com/excel/FirstLast.htm

Gord Dibben MS Excel MVP

[David McRitchie]

Hi kikilein,
I can certainly understand your problem.

You will need to install a macro(s),
See http://www.mvps.org/dmcritchie/excel/join.htm

Separate the Last term (SepLastTerm), then insert a column between first part and last name,
if you want the middle name or initial in it's own column and use SepTerm macro.

Link to install macros in a yellow box at top of web page, but it is
http://www.mvps.org/dmcritchie/excel/getstarted.htm#havemacro

The macros described on that page can all be found in
http://www.mvps.org/dmcritchie/excel/code/join.txt

If you have last names with spaces in them you will probably want to join them with an tilde (~)
or something first, and then replace the tilde with a space when done. Using Replace (Ctrl+H).
i.e. van~Ness van~der~Beck so as not be be confused with a hyphenated names.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"kikilein" <kiki...@discussions.microsoft.com> wrote in message news:1494E2BD-5641-4617...@microsoft.com...

[Mallycat]

Here is some code that will act on the selected text

Sub ParseNames()
Dim myRange As Range
Dim myArray(2), CommaCount, X As Integer
Set myRange = Selection
For Each cell In myRange
CommaCount = 0
myArray(1) = 0
myArray(2) = 0
'count commas
If Len(cell) = 0 Then GoTo ExitHere:
For X = 1 To Len(cell)
If Mid(cell.Text, X, 1) = "," Then
CommaCount = CommaCount + 1
myArray(CommaCount) = X 'store position of comma
End If
Next X
If CommaCount = 1 Then
cell.Offset(0, 1).Value = Left(cell.Value, myArray(1) - 1)
cell.Offset(0, 2).Value = Right(cell.Value, Len(cell) -
myArray(1) - 1)
Else
cell.Offset(0, 1).Value = Left(cell.Value, myArray(1) - 1)
cell.Offset(0, 2).Value = Right(cell.Value, Len(cell) -
myArray(2) - 1)
cell.Offset(0, 3).Value = Mid(cell.Value, myArray(2) - 1,
1)
End If
ExitHere:
Next cell
End Sub

--
Mallycat
------------------------------------------------------------------------
Mallycat's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=35514
View this thread: http://www.excelforum.com/showthread.php?threadid=561786

[Ron Rosenfeld]

On Sat, 15 Jul 2006 16:15:02 -0700, kikilein
<kiki...@discussions.microsoft.com> 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.

You can do this with regular expressions.

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr

Then, assuming the Middle Initial is preceded by a <space>, may optionally be
terminated by a period (.) and is followed by a <space>, you can use the
formula:

=REGEX.MID(A2,"(?<=\s)\w\.?(?=\s)")

If the Middle Initial might be a Middle Name, then:

=REGEX.MID(A2,"(?<=\s)\w+\.?(?=\s)")

would pick up both.

You can also use regular expressions for the first and last names:

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

--ron

[Ron Rosenfeld]

On Sat, 15 Jul 2006 21:42:14 -0400, Ron Rosenfeld <ronros...@nospam.org>
wrote:

I overlooked this in reading your first post:

If the First Name and optional Middle Initial are always followed by a comma
and <space>, then you could use these formulas:

FN: =REGEX.MID(A2,"^\w+(?=,\s)")
MI: =REGEX.MID(A2,"(?<=,\s)\w\.?(?=,\s)")
LN: =REGEX.MID(A2,"\w+$")

--ron

[Mallycat]

To do this in VBA, you can call the Excel VLookup function from within
VBA

Assume A1 has your list box,
the cell link is B1
you have a range with your lookup table 1 banana, 2 apples etc

the this code will give you what you want

Sub test()
Dim myRange As Range
y = Range("B1").Value
Set myRange = Range("Table")
X = Application.WorksheetFunction.VLookup(y, myRange, 2)
End Sub

Regards

Matt

[kikilein]

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).

[kikilein]

MartinW,

I tried using text to column but I will end up with three columns and the
middle column has the middle initial in some cells and the last name from
names without the middle initial in other cells. The name list is huge.
Below please find a sample of the name list:

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

[daddylonglegs]

You haven't really said what you want to do,

If you want a column of first names, a column of surnames and a column
of initials (when they exist) you could use these formulas in B1, C1
and D1 for data in A1

=LEFT(A1,FIND(" ",A1)-1)

=REPLACE(A1,1,FIND(" ",A1)+(ISNUMBER(FIND(".",A1)))*3,"")

=IF(ISNUMBER(FIND(".",A1)),MID(A1,FIND(" ",A1)+1,1),"")

--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30486

[kikilein]

Hi Gord,

Thanks for the site. Maybe I am totally unable to think the formula through
and that is why I can not get it to work.

When I paste either of the formulas into into cell B2 (the firstname, middle
initial, lastname is in A2) the result I am getting is the same name
including firstname, middle initial, lastname. Nothing is being seperated.
What am I doing wrong?

[kikilein]

daddylonglegs,

I am getting soooo close with your formulas. One is extracting the first
name correctly and the other extracts the last name. However, I am still
having trouble seperating the middle initial and the formula:

=IF(ISNUMBER(FIND(".",A1)),MID(A1,FIND(" ",A1)+1,1),"")

is giving me nothing (the cell stays empty.

To clarify. I have a long list of names. For example:

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

I would like to end up with three columns. One with firstname, one with
middle initial (if there is one) and one with the lastname.

Thank you very much for trying to help (all of you).

[kikilein]

Mallycat,

Forgive me, but how am I going to use the code? Please explain (remember I
am not that advanced). Where do I paste this code? Is it a macro?

Thanks so much.

[daddylonglegs]

kikilein Wrote:
>
> However, I am still
> having trouble seperating the middle initial and the formula:
>
> =IF(ISNUMBER(FIND(".",A1)),MID(A1,FIND(" ",A1)+1,1),"")
>
> is giving me nothing (the cell stays empty.
>

Assuming you have a . (full stop/period) immediately after the middle
initial the formula above should give that initial, otherwise it
returns nothing.

E.g. for

Karen K. Canterberry in A1 that formula should return K

for

Karen Canterberry in A1 that formula should return nothing

alternatively try

=IF(ISNUMBER(FIND(".",A1)),MID(A1,FIND(".",A1)-1,1),"")

[kikilein]

Ok, I fiddled a little and all of the sudden I had a new macro named
"ParseNames" :-), which is probably what you called it, Mallycat.

When I run this macro I am getting a debug window and it tells me to debug
at the the line that I enclosed with tripple asteriks *** (seventh line from
the bottom). The line is colored yellow in the de-bugger - not sure if that
means anything:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 7/16/2006 by kikilein
'

'

Sub ParseNames()
Dim myRange As Range
Dim myArray(2), CommaCount, X As Integer
Set myRange = Selection
For Each cell In myRange
CommaCount = 0
myArray(1) = 0
myArray(2) = 0
'count commas
If Len(cell) = 0 Then GoTo ExitHere:
For X = 1 To Len(cell)
If Mid(cell.Text, X, 1) = "," Then
CommaCount = CommaCount + 1
myArray(CommaCount) = X 'store position of comma
End If
Next X
If CommaCount = 1 Then
cell.Offset(0, 1).Value = Left(cell.Value, myArray(1) - 1)
cell.Offset(0, 2).Value = Right(cell.Value, Len(cell) - myArray(1) - 1)
Else

***cell.Offset(0, 1).Value = Left(cell.Value, myArray(1) - 1)***

cell.Offset(0, 2).Value = Right(cell.Value, Len(cell) - myArray(2) - 1)
cell.Offset(0, 3).Value = Mid(cell.Value, myArray(2) - 1, 1)
End If
ExitHere:
Next cell
End Sub

[Gord Dibben]

The formulas on Chip's site assume you have a comma in the original cell.

i.e. Last, First MI

Did you download the workbook?

You can see more clearly and formulas are a little easier to copy.

Gord

[kikilein]

Yes, the middle initial has a period right next to it. I just tried it again
and I am not sure what I am doing wrong. It is still zero in all the cells.
Any ideas?

[kikilein]

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.

[Gord Dibben]

Great to hear!!!!

Gord

On Sun, 16 Jul 2006 10:53:01 -0700, kikilein

[Ron Rosenfeld]

On Sun, 16 Jul 2006 07:38:01 -0700, kikilein
<kiki...@discussions.microsoft.com> wrote:

>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).

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)")

[kikilein]

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....