asked on

Excel 2010 split name

I have a list of names in one column (First/Last) that I want to split into two columns.
The problem is some have middle initials and some names do not.
The names are list in column A, example name in cell A1: John Q Public
I inserted the following formula in Cell B1: =LEFT(A1,FIND(" ",A1,1)-1)
The problem is the middle initial gets moved to Column C along with the last name.
I would like to moved to its own Column. How can do this? Thanks.

Martin Liss

You can build on the solution I posted in this thread.

Justicator

Making assumption that all names are properly formatted and spaced, you can simply do Text to Columns.

Data --> Text to Columns. Use Delimited, and select by "Space". Select "Treat consecutive delimiters as one".

Run through the rest of the wizard to make sure it works, and let it rip!

The only thing that would break this is having multi name first / last names, but it also breaks any other solution as well.

Martin Liss

Forget my previous post. If Justicator's solution doesn't work or if it's not suitable then use this macro.

Function SplitName()

Dim strParts() As String
Dim LastRowColA As Long
Dim lngRow As Long

LastRowColA = Range("A65536").End(xlUp).Row

For lngRow = 2 To LastRowColA
    strParts = Split(Range("A" & lngRow).Value, " ")
    If UBound(strParts) = 2 Then
        Range("B" & lngRow).Value = strParts(0)
        Range("C" & lngRow).Value = strParts(1)
        Range("D" & lngRow).Value = strParts(2)
    Else
        Range("B" & lngRow).Value = strParts(0)
        Range("D" & lngRow).Value = strParts(1)
    End If
Next

End Function

Open in new window