separating text from cells

G

Guest

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

Guest

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??
 
M

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
 
D

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

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
 
R

Ron Rosenfeld

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.
Click to expand...

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
 
R

Ron Rosenfeld

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
Click to expand...

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
 
M

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
 
G

Guest

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

Guest

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
 
D

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

Guest

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?
 
G

Guest

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

Guest

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

daddylonglegs

kikilein said:
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.
Click to expand...

Assuming you have a . (full stop/period) immediately after the middl
initial the formula above should give that initial, otherwise i
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),""
 
G

Guest

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
 
G

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
 
G

Guest

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?
 
G

Guest

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

complicated text to column 4
Formula To Split Name 5
Separate Middle Initial From First Name 9
Excel: Sort alphabetically using the last word in a cell? 1
Separate First, Last Names 2
Reversing Last Name and First Names 2
Remove initial from end of name 8
Concatenate first, middle and last names 1

Top