How to parse full name into first, mi, and last

Posted on 2006-05-30
Last Modified: 2010-05-18
I have a big list of names in a single Excel column.  Some of them have first, mi, and last.  Others don't have the middle initial.  

John Smith
Nancy H. Drew
Tom R Brown

Notice that some of the middle initials have a period and other don't.  I want to parse this field into 3 separate fields.  I've done something similar in the past using the instr function to determine where the spaces are and then using left, right, and mid.  But I don't see instr in excel.  Any ideas?
Question by:tdeinzer2005
    LVL 9

    Expert Comment

    The Instr function is availble in VBA...
    LVL 2

    Expert Comment

    Assuming full name is in column A, starting on row 1.

    First name:
    =LEFT($A1,FIND(" ",TRIM($A1))-1)

    Middle Initial:
    =MID($A2,FIND(" ",TRIM($A2))+1,IF(ISERROR(SEARCH(" ",$A2,FIND(" ",TRIM($A2))+1)-(FIND(" ",TRIM($A2))+1)),0,SEARCH(" ",$A2,FIND(" ",TRIM($A2))+1)-(FIND(" ",TRIM($A2))+1)))

    Last Name:
    =RIGHT($A1,LEN($A1)-(MAX(FIND(" ",$A1),IF(ISERROR(SEARCH(" ",$A1,FIND(" ", $A1)+1)),0,SEARCH(" ",$A1,FIND(" ", $A1)+1)))))
    LVL 2

    Expert Comment

    Sorry, replace 2 with 1 for Middle initial.  I copied from the wrong cell on that one.
    LVL 2

    Expert Comment

    Oh, I forgot a few TRIMs so here it is all cleaned up:

    =LEFT(TRIM($A1),FIND(" ",TRIM($A1))-1)

    =MID(TRIM($A1),FIND(" ",TRIM($A1))+1,IF(ISERROR(SEARCH(" ",TRIM($A1),FIND(" ",TRIM($A1))+1)-(FIND(" ",TRIM($A1))+1)),0,SEARCH(" ",TRIM($A1),FIND(" ",TRIM($A1))+1)-(FIND(" ",TRIM($A1))+1)))

    =RIGHT(TRIM($A1),LEN(TRIM($A1))-(MAX(FIND(" ",TRIM($A1)),IF(ISERROR(SEARCH(" ",TRIM($A1),FIND(" ", TRIM($A1))+1)),0,SEARCH(" ",TRIM($A1),FIND(" ", TRIM($A1))+1)))))
    LVL 44

    Accepted Solution

    Instead of all this code, ALL you have to do is export the single field using a SPACE as a field delimiter, and nothing as the field separator, and write the whole lot to a CSV file.  WOW!!  Now you have 3 totally separate fields out of a single field.  Now all you have to do is IMPORT that field array into a table, using the SPACE as separator, and nothing as the field delimiter.  Presto, you have 3 fields whereas previously you only had ONE !!!
    LVL 2

    Expert Comment

    The sarcasm is unwarranted as the original poster specified that not all records contain a middle initial.
    LVL 65

    Expert Comment

    A VBA function that can be used without having to use INSTR

    'Define your stuff here
    Const START_ROW = 1      'start row of names column
    Const START_COL = 1       'column holding names column
    Const FN_COL = 3              'column for first name
    Const MN_COL = 4
    Const LN_COL = 5

    'Assumes data is written to the same row
    'this code in the sheet that your interested in modifying

    Public Sub SplitNames()

        Dim iRow As Integer
        Dim sNames() As String
        'Assume a blank cell is end of data
        iRow = START_ROW
        Do While Cells(iRow, START_COL) <> ""
            Debug.Print "Processing ", Cells(iRow, START_COL)

            'Split names into array using Space as the delimiter
            sNames = Split(Cells(iRow, START_COL), " ")

            'Store the names in the individual name column        
            Cells(iRow, FN_COL) = sNames(0)
            If UBound(sNames) = 1 Then 'no middlename
                Cells(iRow, LN_COL) = sNames(1)
                Cells(iRow, MN_COL) = sNames(1)
                Cells(iRow, LN_COL) = sNames(2)
            End If
            iRow = iRow + 1
    End Sub


    Author Comment

    I had already found the answer before I had read the responses.  I completely forgot about the "Text to Columns" feature!!!  I was also thinking of writing crazy code for this.  Scrathcyboy was the closest to this simple solution.
    LVL 2

    Expert Comment

    @tdeinzer2005 -
    How are you handling the fact that some names do not have a middle initial?
    LVL 44

    Expert Comment

    no sarcasm was intended.  Glad that was a useful solution.
    LVL 1

    Expert Comment

    pyroman's help was much better as it deals with the MI issue. Thanks pyro
    LVL 1

    Expert Comment

    thank you.
    LVL 1

    Expert Comment

    oh and pyroman1 became tamco recently.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Workplace bullying has increased with the use of email and social media. Retain evidence of this with email archiving to protect your employees.
    Healthcare organizations in the United States must adhere to the guidance of both the HIPAA (Health Insurance Portability and Accountability Act) and HITECH (Health Information Technology for Economic and Clinical Health Act) for securing and protec…
    The viewer will learn common shortcuts with easy ways to remember them. The viewer will then learn where to find all of the keyboard shortcuts, how to create/change them, and how to speed up their workflow.
    The viewer will learn how to successfully download and install the SARDU utility on Windows 8, without downloading adware.

    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

    22 Experts available now in Live!

    Get 1:1 Help Now