Text to columns at last space only

**mieuxvivre** · 05-07-2013, 07:22 AM

I have a column that has multiple words, mostly names. I need to split them into two columns at the last space.

Examples:

Joe Doe
Joe & Jane Doe
Philip C. Merry
My Great Company

I need to have the names split as follows.

Column 1       Column 2
Joe            Doe
Joe & Jane     Doe
Philip C.      Merry
My Great       Company

My name column is currently starting at B2. I have seen instructions how to split at first space or how to split into First, Last and Middle names but I only need to split at the last space. Thanks in advance for your help.

**martindwilson** · 05-07-2013, 07:40 AM

put
=TRIM(LEFT(SUBSTITUTE(B2," ",REPT(" ",25)),LEN(SUBSTITUTE(B2," ",REPT(" ",25)))-25)) in c2 and
=TRIM(RIGHT(SUBSTITUTE(B2," ",REPT(" ",25)),25)) in d2

**Kevin UK** · 05-07-2013, 08:13 AM

Hi mieuxvivre

Try the following.
In D2:

Formula:

=TRIM(RIGHT(SUBSTITUTE(B2," ",REPT(" ",LEN(B2))),LEN(B2)))

Then in C2:

Formula:

=SUBSTITUTE(B2,D2,"")

**im.zeeshan** · 05-07-2013, 08:45 AM

I'm assuming you have placed all the Full Names in Column A starting from Row 2.

Once you have done that, run the following macro:

Sub SeparateFirstAndLastName()
    Cells(1, 2).Value = "First Name"
    Cells(1, 3).Value = "Last Name"
    For i = 2 To Sheets(1).UsedRange.Count
        aFullName = Split(Cells(i, 1).Value, " ")
        sFirstName = ""
        For j = 0 To UBound(aFullName)
            If j < UBound(aFullName) Then
                sFirstName = sFirstName & " " & aFullName(j)
            Else
                sLastName = aFullName(j)
            End If
        Next
        Cells(i, 2).Value = sFirstName
        Cells(i, 3).Value = sLastName
    Next
End Sub