Separating names into rows using formulas

[jeffcoleky]

I have thousands of names I need to separate into rows using formulas. This will be a challenge because of the naming format:

I have it MOSTLY down but I'm stuck because my first names get put into the middle name column when there is no middle initial.

SAMPLE DATA:

A		B	C	D
Full Name	First	Middle	Last
SMITH BOB E	Bob	E	Smith
FRENCH PIERRE S	Pierre	S	French
CRICKET JIMMY		Jimmy	Cricket
GOLD POT OF	Pot	Of	Gold
LATER CHARLIE U	Charlie	U	Later

First Name Formula: =PROPER(IF(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))=1,"",MID(A2,FIND(" ",A2,1)+1,FIND(MID(A2,FIND("~",SUBSTITUTE(A2," ","~",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))+1,255),A2,1)-2-FIND(" ",A2,1))))

MIDDLE NAME FORMULA: =PROPER(REPLACE(A2,1,SEARCH("^",SUBSTITUTE(A2," ","^",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))),""))

LAST NAME FORMULA: =PROPER(LEFT(A2,SEARCH(" ",A2)-1))


YOUR HELP IS MUCH APPRECIATED!

 

[AlphaFrog]

Try something like this...

<br /><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Full Name</td><td style="font-weight: bold;;">First</td><td style="font-weight: bold;;">Middle</td><td style="font-weight: bold;;">Last</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">SMITH BOB E</td><td style=";">Bob</td><td style=";">E</td><td style=";">Smith</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">FRENCH PIERRE S</td><td style=";">Pierre</td><td style=";">S</td><td style=";">French</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">CRICKET JIMMY</td><td style=";">Jimmy</td><td style=";"></td><td style=";">Cricket</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">GOLD POT OF</td><td style=";">Pot</td><td style=";">Of</td><td style=";">Gold</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">LATER CHARLIE U</td><td style=";">Charlie</td><td style=";">U</td><td style=";">Later</td></tr></tbody></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B2</th><td style="text-align:left">=IF(<font color="Blue">$A2="","",PROPER(<font color="Red">TRIM(<font color="Green">MID(<font color="Purple">SUBSTITUTE(<font color="Teal">$A2," ",REPT(<font color="#FF00FF">" ",50</font>)</font>),50,50</font>)</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C2</th><td style="text-align:left">=IF(<font color="Blue">$A2="","",IF(<font color="Red">LEN(<font color="Green">A2</font>)-LEN(<font color="Green">SUBSTITUTE(<font color="Purple">$A2," ",""</font>)</font>)=1,"",PROPER(<font color="Green">TRIM(<font color="Purple">MID(<font color="Teal">SUBSTITUTE(<font color="#FF00FF">$A2," ",REPT(<font color="Navy">" ",50</font>)</font>),100,50</font>)</font>)</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D2</th><td style="text-align:left">=IF(<font color="Blue">$A2="","",PROPER(<font color="Red">TRIM(<font color="Green">MID(<font color="Purple">SUBSTITUTE(<font color="Teal">$A2," ",REPT(<font color="#FF00FF">" ",50</font>)</font>),1,50</font>)</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

 

[jeffcoleky]

"Do or do no, there is no TRY."

WOW! I tried it and it worked PERFECTLY. Thank you SOOOooo much!

 

[AlphaFrog]

Welcome are you.

 

[jeffcoleky]

Can i make it a little--no, a LOT more complicated? It's functioning as it is but if there were a way to also add this next portion, it would REALLY help.

Add the following married couples' names to the list:

JEFFREYS RON & PAT
JEFFREYS RON & PAT E
BAKER BOB E & JONES SARAH
BAKER BOB E & JONES SARAH A
BAKER BOB E & JONES SARAH ASHLEY

When there is a married couple, an "&" is used each time to connect their names. The second name, if there is a middle name, will ALWAYS only be the person's middle initial. So if you see two full names after the "&", then you know the first of the two is a last name.

By adding the spouse, i needed three more columns. See below along with the desired result:

Excel Workbook
	A	B	C	D	E	F	G
1	Full Name	First	Middle	Last	First2	Middle2	Last2
2	SMITH BOB E	Bob	E	Smith
3	FRENCH PIERRE S	Pierre	S	French
4	CRICKET JIMMY	Jimmy		Cricket
5	GOLD POT OF	Pot	Of	Gold
6	LATER CHARLIE U	Charlie	U	Later
7	JEFFREYS RON & PAT	Ron		Jeffreys	Pat		Jeffreys
8	JEFFREYS RON & PAT E	Ron		Jeffreys	Pat	E	Jeffreys
9	BAKER BOB E & JONES SARAH	Bob	E	Baker	Sarah		Jones
10	BAKER BOB E & JONES SARAH A	Bob	E	Baker	Sarah	A	Jones
11	BAKER BOB E & JONES SARAH ASHLEY	Bob	E	Baker	Sarah	Ashley	Jones
Sheet1

Excel 2010

Note that in the instances where the second name does not have a different last name, the surname in column D would be reused in column G.

 

[jeffcoleky]

i forgot to add: if you need more columns to make this work, that's not a problem. I'm clueless on how to make this work though.

 

[AlphaFrog]

It could be done with just formulas albeit lengthy ones in columns E and F. I do have questions about your procedures though.

You did a good job in describing how you want the names split. It may be easier to just do it with just a macro instead of formulas. Would a macro solution work for you?

If you can't use a macro, could you do a Text to Columns split on column A using the "&" as the delimiter so as to split the married couples into two columns (A and B)? That would greatly shorten the formulas.

 

[jeffcoleky]

It could be done with just formulas albeit lengthy ones in columns E and F. I do have questions about your procedures though.

You did a good job in describing how you want the names split. It may be easier to just do it with just a macro instead of formulas. Would a macro solution work for you?

If you can't use a macro, could you do a Text to Columns split on column A using the "&" as the delimiter so as to split the married couples into two columns (A and B)? That would greatly shorten the formulas.

Macros would be preferred actually. Why did I say otherwise at the beginning? I have no idea--probably because I thought I already had most of it done with formulas... In any case, yes, macros are great. Thank you.

I don't want to do text to columns for control and speed purposes...

 

[alansidman]

crosspost http://www.excelforum.com/excel-gen...-mid-and-last-first-mid-separate-columns.html

 

[AlphaFrog]

Sub Name_Splitter()
    
    Dim rng As Range, cell As Range, Names As Variant, NameParts As Variant
    
    Set rng = Range("A2", Range("A" & Rows.Count).End(xlUp))
    
    Application.ScreenUpdating = False
    
    rng.Offset(, 1).Resize(, 6).ClearContents
    
    For Each cell In rng
        If cell.Value <> "" Then
            Names = Split(Application.WorksheetFunction.Trim(cell.Value), " & ")
            
            ' Single name or 1st of Couple
            NameParts = Split(Names(0), " ")
            cell.Offset(, 1).Value = StrConv(NameParts(1), vbProperCase)
            If UBound(NameParts) = 2 Then cell.Offset(, 2).Value = StrConv(NameParts(2), vbProperCase)
            cell.Offset(, 3).Value = StrConv(NameParts(0), vbProperCase)
            
            ' 2nd name if Couple
            If UBound(Names) > 0 Then
                NameParts = Split(Names(1), " ")
                If UBound(NameParts) = 0 Then
                    ' Pat
                    cell.Offset(, 4).Value = StrConv(NameParts(0), vbProperCase)
                    cell.Offset(, 6).Value = cell.Offset(, 3).Value
                ElseIf UBound(NameParts) = 1 And Len(NameParts(1)) = 1 Then
                    ' Pat E
                    cell.Offset(, 4).Value = StrConv(NameParts(0), vbProperCase)
                    cell.Offset(, 5).Value = UCase(NameParts(1))
                    cell.Offset(, 6).Value = cell.Offset(, 3).Value
                Else
                    ' Jones Sarah  or  Jones Sarah A
                    cell.Offset(, 4).Value = StrConv(NameParts(1), vbProperCase)
                    If UBound(NameParts) = 2 Then cell.Offset(, 5).Value = StrConv(NameParts(2), vbProperCase)
                    cell.Offset(, 6).Value = StrConv(NameParts(0), vbProperCase)
                End If
            End If
        End If
    Next cell
    
    Application.ScreenUpdating = True

End Sub