Excel 2010 split name

Posted on 2012-08-15
Last Modified: 2012-08-15
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.
Question by:fjkaykr11
    LVL 44

    Expert Comment

    by:Martin Liss
    You can build on the solution I posted in this thread.
    LVL 4

    Expert Comment

    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.
    LVL 44

    Expert Comment

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

    Open in new window

    LVL 92

    Expert Comment

    by:Patrick Matthews
    LVL 26

    Accepted Solution

    Hi, jkaykr11.

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

    LVL 3

    Author Comment

    @redmond thanks so much.  this is exactly what I needed.
    LVL 3

    Author Closing Comment

    Thanks for the sample spreadsheet. works great.
    LVL 26

    Expert Comment

    Thanks, jkaykr11.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now