Full Name to First | Middle | Last

[wteags]

I have about 5,000 Full Names in one column. I would like to separate these into first, middle and last name. Currently the names are like John Doe, David P. Smith, Mary Sue Johnson. I have tried successfully to extract the first name using:



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



Using another function



=MID(C6,FIND(" ",C6)+1,LEN(C6)+1-FIND(" ",C6))



I get the Middle and last names. However, when I try to separate the middle and last names further, the functions I have tried will return #VALUE if there is no second name. I guess I am out of ideas.

[Rick M]

Try using Text to column method.
Select the column with the names (make sure the columns next to it are empty for now)
then TOOLS>DATA>Text to Column>Delimited>next> (select delimiter such as Space>finish

Hope that works!

[wteags]

I think that will work. I had tried it before, but with no luck. Problem was that I was trying to separate data that was imported from another worksheet (C6 =Workup!C2). I will go in and massage the original data. Thanks!

[Seti]

How about this one:

Name is in B4
In C4 (first name) use what you already had
=LEFT(B4,FIND(" ",B4)-1)

In D4 (middle name or initial) use
=IF(ISERR(FIND(" ",B4,1+FIND(" ",B4))),"",MID(B4,FIND(" ",B4)+1,FIND(" ",B4,1+FIND(" ",B4))-FIND(" ",B4)-1))

In E4 (last name) use
=IF(ISERR(FIND(" ",B4,1+FIND(" ",B4))),MID(B4,FIND(" ",B4)+1,LEN(B4)),MID(B4,1+FIND(" ",B4,1+FIND(" ",B4)),LEN(B4)))

This will put a blank in middle name if there are only first and last names. It will not work if you have Jr. or II/III in the names and no middle initial. You could extrapolate these formulas to handle it by looking at the number of blanks though.

I have taken advantage of the fact that the FIND function takes an optional 3rd argument.

If you use the Text to Columns option, note that when you have only a first and last name, the last name will be in the middle name column and will require the manual adjustments you alluded to.

HTH.

Seti

[gareth]

This code was given to me by Chris Wendel.Bless his soul.Here it is my friend.modify so it can suit your needs.

Function ReversOrder(Cell As String) As String

' Created by Henrik Wendel
' This function works only on text string not number like 111 222 _
the space will dissepear


Dim SpacePos As Integer ' To keep the position of the separator (this case SPACE )
Dim First As String ' String to contain the first part
Dim Sec As String 'String to contain the second part
Dim LenCell As Integer 'to keep total lenght of string to revers


LenCell = Len(Cell) 'Get the string lenght

SpacePos = InStr(1, Cell, " ", vbTextCompare) ' find where to separate string _
character between the double qoutes is the char. to separate at. This case a SPACE

If Not SpacePos = 0 Then 'Error handling the possibillity that _
there is no separator char. in the string

First = Left(Cell, SpacePos - 1) 'Get the first part

Sec = Mid(Cell, SpacePos + 1, LenCell) 'Get the second part

Cell = Sec & " " & First 'Put second part first and the separator char. _
back but after second instead of first. An finaly put first part last _
character between the double qoutes _
is the char. to separate at. This case a SPACE

ReversOrder = Cell ' Return new string to function
Else
ReversOrder = Cell ' This is what happens if spacepos = 0 (Nothing) _
just set string to what it was
End If

End Function