?
Solved

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

Posted on 2006-05-30
13
Medium Priority
?
1,779 Views
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.  

Examples:
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?
0
Comment
Question by:tdeinzer2005
  • 5
  • 2
  • 2
  • +4
13 Comments
 
LVL 9

Expert Comment

by:AndreDekolta
ID: 16792935
The Instr function is availble in VBA...
0
 
LVL 2

Expert Comment

by:pyroman1
ID: 16793051
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)))))
0
 
LVL 2

Expert Comment

by:pyroman1
ID: 16793056
Sorry, replace 2 with 1 for Middle initial.  I copied from the wrong cell on that one.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 2

Expert Comment

by:pyroman1
ID: 16793087
Oh, I forgot a few TRIMs so here it is all cleaned up:

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

MI:
=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)))

LN:
=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)))))
0
 
LVL 44

Accepted Solution

by:
scrathcyboy earned 1500 total points
ID: 16795620
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 !!!
0
 
LVL 2

Expert Comment

by:pyroman1
ID: 16795687
The sarcasm is unwarranted as the original poster specified that not all records contain a middle initial.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16797010
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)
        Else
            Cells(iRow, MN_COL) = sNames(1)
            Cells(iRow, LN_COL) = sNames(2)
        End If
       
        iRow = iRow + 1
    Loop
End Sub

0
 

Author Comment

by:tdeinzer2005
ID: 16798128
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.
0
 
LVL 2

Expert Comment

by:pyroman1
ID: 16798651
@tdeinzer2005 -
How are you handling the fact that some names do not have a middle initial?
0
 
LVL 44

Expert Comment

by:scrathcyboy
ID: 16800510
no sarcasm was intended.  Glad that was a useful solution.
0
 
LVL 1

Expert Comment

by:griffonage
ID: 22789356
pyroman's help was much better as it deals with the MI issue. Thanks pyro
0
 
LVL 1

Expert Comment

by:tamco
ID: 22789562
thank you.
0
 
LVL 1

Expert Comment

by:tamco
ID: 22789568
oh and pyroman1 became tamco recently.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There can be many situations demanding the conversion of Outlook OST files to PST format and as such, there is no shortage of automated tools to perform this conversion. However, what makes Stellar OST to PST converter stand above the rest? Let us e…
Today as you open your Outlook, you witness an error message: “Outlook is using an old copy of your Outlook Data File…”. Probably, Outlook is accessing an old OST file.
The viewer will learn how to successfully download and install the SARDU utility on Windows 8, without downloading adware.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

839 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