Convert this Name-Separating Macro into Formulas?

[jeffcoleky]

The goal is to turn This:
Excel 2010

	A
Names Pre-Split
Armstrong Charles A Sr
Brackney Michael & Robin
Capland Yosef & Chava N
Fowler Kenneth
Kimbrough Vanessa G
Thammachart Suwanit & Gagel Tabitha

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

</tbody>

Sheet1​

Into This:
Excel 2010

	B	C	D	E
First Name 1	Last Name 1	First Name 2	Last Name 2
Charles	Armstrong
Michael	Brackney	Robin	Brackney
Yosef	Capland	Chava	Capland
Kenneth	Fowler
Vanessa	Kimbrough
Suwanit	Thammachart	Tabitha	Gagel

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

</tbody>

Sheet1​

Here is the MACRO that does this for me: (Note, the macro also adds columns for middle names/initials which I wish to exclude as well)

Sub Last_First_Split()

    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

This is a great macro and it has worked great for me but now our needs have changed and I need formulas to be used in columns B, C, D, & E instead of the macro. Can anyone help me convert it?

EXCEL FILE HERE:http://goo.gl/ZY1xI

​

 

[hellomynameiskristin]

Try this:
[TABLE="width: 157"]
<TBODY>[TR]
[TD]If this is in D9:[TABLE="width: 157"]
<TBODY>[TR]
[TD]Brackney Michael & Robin


Last name formula: =LEFT(D9,FIND(" ",D9)-1)


First First name formula:=MID(D9,FIND(" ",D9)+1,FIND(" ",D9)-1)

2nd First Name Formula: =MID(D9,FIND("&",D9)+1,FIND(" ",D9)-1)[/TD]
[/TR]
</TBODY><COLGROUP><COL></COLGROUP>[/TABLE]

[/TD]
[/TR]
</TBODY><COLGROUP><COL></COLGROUP>[/TABLE]

 

[hellomynameiskristin]

Try this:
If this is in D9:Brackney Michael & Robin


Last name formula: =LEFT(D9,FIND(" ",D9)-1)


First First name formula:=MID(D9,FIND(" ",D9)+1,FIND(" ",D9)-1)

2nd First Name Formula: =MID(D9,FIND("&",D9)+1,FIND(" ",D9)-1)

 

[jeffcoleky]

Try this:

Thank you but I will need something more complex than that due to the varying names in column A. I can't figure out how to adapt it based upon all the different possibilities.

The format could be one of several ways. I'll use the same name in all ways it might show up:

Excel 2010

	A	B
Names Pre-Split	Variation
Brackney Michael	No middle name, one person only
Brackney Michael Frank	Middle name included, one person only
Brackney Michael & Robin	No middle names, same last name
Brackney Michael & Brackney Robin	No middle inames, last name listed twice
Brackney Michael & Jones Robin	Same as above, different last names
Brackney Michael & Brackney Robin Sara	Same Last Name, middle name/initial for robin listed
Brackney Michael & Jones Robin Sara	Same as above, different last names
Brackney Michael F & Brackney Robin Sara	Same Last Name, Middle Name/initial for both
Brackney Michael F & Jones Robin Sara	Same as above, different last names

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]10[/TD]

</tbody>

Sheet1​

(Middle name not needed)


The formulas you provided are great for when only the first line is true but what do we do for all the other variations, just like the macro did?