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.

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.

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

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


On Sat, 15 Jul 2006 16:15:02 -0700, 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.

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

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 ExitHe
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
ExitHe
Next cell
End Sub


--
Mallycat
------------------------------------------------------------------------
Mallycat's Profile: http://www.excelforum.com/member.php...o&userid=35514
View this thread: http://www.excelforum.com/showthread...hreadid=561786

On Sat, 15 Jul 2006 16:15:02 -0700, 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.

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

On Sat, 15 Jul 2006 21:42:14 -0400, Ron Rosenfeld
wrote:

On Sat, 15 Jul 2006 16:15:02 -0700, 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.

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

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

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


--
Mallycat
------------------------------------------------------------------------
Mallycat's Profile: http://www.excelforum.com/member.php...o&userid=35514
View this thread: http://www.excelforum.com/showthread...hreadid=561786

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

"Ron Rosenfeld" wrote:

On Sat, 15 Jul 2006 21:42:14 -0400, Ron Rosenfeld
wrote:

On Sat, 15 Jul 2006 16:15:02 -0700, 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.

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

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

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

"MartinW" wrote:

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

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...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=561786

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?

"Gord Dibben" wrote:

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


On Sat, 15 Jul 2006 16:15:02 -0700, 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.

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



"daddylonglegs" wrote:


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...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=561786

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.


"Mallycat" wrote:


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 ExitHe
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
ExitHe
Next cell
End Sub


--
Mallycat
------------------------------------------------------------------------
Mallycat's Profile: http://www.excelforum.com/member.php...o&userid=35514
View this thread: http://www.excelforum.com/showthread...hreadid=561786

On Sun, 16 Jul 2006 07:38:01 -0700, kikilein
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)")
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....

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