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.
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

redmondbConnect With a Mentor Commented:
Hi, jkaykr11.

Please see attached for a pure (unfortunately ugly!) formula solution.

Martin LissOlder than dirtCommented:
You can build on the solution I posted in this thread.
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.
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Martin LissOlder than dirtCommented:
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)
        Range("B" & lngRow).Value = strParts(0)
        Range("D" & lngRow).Value = strParts(1)
    End If

End Function

Open in new window

fjkaykr11Author Commented:
@redmond thanks so much.  this is exactly what I needed.
fjkaykr11Author Commented:
Thanks for the sample spreadsheet. works great.
Thanks, jkaykr11.
All Courses

From novice to tech pro — start learning today.