Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 208
Default parsing a full name out into Last, First, Middle

Hi,

I have many full names separated by spaces in many rows of column A. Some
full names have middle names or middle initials while some have a comma at
the end of their name, followed by a job title...if there is a comma at the
end of their name, then it is followed by a job title.

I want the last name of each person separated out of col A and displayed in
col B of the same row, the first name separated out of col A and displayed in
col C of the same row, the middle initial or middle name separated out of col
A and displayed in col D of the same row OR col D would be blank if the name
in col A does not have a middle initial or name, and the job title separated
out of col A and displayed in Col E of the same row OR col E would be blank
if the name in col A does not have a job title.

For example, I need a function that can separate out full names in column A
cells such as:

"John Smith" in cell A1 into 2 separate cells with "Smith" in B1 and "John"
in C1 with nothing in cell D1 as there is no Middle name or middle initial
and nothing in cell E1 as there is no job title

"John Q. Smith" in cell A2 into 3 separate cells with "Smith" in B2 "John"
in C2 and "Q." in D2 with nothing in E2 as there is no job title

"John Quincy Smith" in cell A3 into 3 separate cells with "Smith" in B3
"John" in C3 and "Quincy" in D3 with nothing in E3 as there is no job title

"John Q. Smith, Exec. Dir." in cell A4 into 4 separate cells with "Smith" in
B4 "John" in C4 "Q." in D4 and "Exec. Dir." in E4

"John Q. Smith, Executive Director" in cell A5 into 4 separate cells with
"Smith" in B5 "John" in C5 "Q." in D5 and "Executive Director" in E5

"John Smith, Executive" in cell A6 into 3 separate cells with "Smith" in B6
"John" in C6 with nothing in D6 as there is no middle name or middle initial
and "Executive" in E6

Thank you very much for any assistance!

Craig
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,124
Default parsing a full name out into Last, First, Middle


http://www.cpearson.com/excel/FirstLast.htm
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Craig" wrote in message
...
Hi,

I have many full names separated by spaces in many rows of column A. Some
full names have middle names or middle initials while some have a comma at
the end of their name, followed by a job title...if there is a comma at
the
end of their name, then it is followed by a job title.

I want the last name of each person separated out of col A and displayed
in
col B of the same row, the first name separated out of col A and displayed
in
col C of the same row, the middle initial or middle name separated out of
col
A and displayed in col D of the same row OR col D would be blank if the
name
in col A does not have a middle initial or name, and the job title
separated
out of col A and displayed in Col E of the same row OR col E would be
blank
if the name in col A does not have a job title.

For example, I need a function that can separate out full names in column
A
cells such as:

"John Smith" in cell A1 into 2 separate cells with "Smith" in B1 and
"John"
in C1 with nothing in cell D1 as there is no Middle name or middle initial
and nothing in cell E1 as there is no job title

"John Q. Smith" in cell A2 into 3 separate cells with "Smith" in B2 "John"
in C2 and "Q." in D2 with nothing in E2 as there is no job title

"John Quincy Smith" in cell A3 into 3 separate cells with "Smith" in B3
"John" in C3 and "Quincy" in D3 with nothing in E3 as there is no job
title

"John Q. Smith, Exec. Dir." in cell A4 into 4 separate cells with "Smith"
in
B4 "John" in C4 "Q." in D4 and "Exec. Dir." in E4

"John Q. Smith, Executive Director" in cell A5 into 4 separate cells with
"Smith" in B5 "John" in C5 "Q." in D5 and "Executive Director" in E5

"John Smith, Executive" in cell A6 into 3 separate cells with "Smith" in
B6
"John" in C6 with nothing in D6 as there is no middle name or middle
initial
and "Executive" in E6

Thank you very much for any assistance!

Craig


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,651
Default parsing a full name out into Last, First, Middle

On Tue, 28 Jul 2009 14:06:01 -0700, Craig
wrote:

I have many full names separated by spaces in many rows of column A. Some
full names have middle names or middle initials while some have a comma at
the end of their name, followed by a job title...if there is a comma at the
end of their name, then it is followed by a job title.

I want the last name of each person separated out of col A and displayed in
col B of the same row, the first name separated out of col A and displayed in
col C of the same row, the middle initial or middle name separated out of col
A and displayed in col D of the same row OR col D would be blank if the name
in col A does not have a middle initial or name, and the job title separated
out of col A and displayed in Col E of the same row OR col E would be blank
if the name in col A does not have a job title.


This cannot be done completely with full accuracy because of the tremendous
variability in names. If a person has just a single first name, and a single
last name, then it is possible.

But what about

Mary Ann June Foster Smith
Julio de la Cortez

And so forth.

In any event, here is a UDF that should do some of the work.

To enter this Macro (Sub), <alt-F11 opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

Alter the "Set rg =" line to reflect the range you wish to parse. The result
will appear adjacent.

To use this Macro (Sub), <alt-F8 opens the macro dialog box. Select the macro
by name, and <RUN.

=======================================
Option Explicit
Sub ParseName()
Dim s As String
Dim rg As Range, c As Range
Dim re As Object, mc As Object, m As Object
Dim i As Long

Set rg = Range("A2:A10")
Set re = CreateObject("vbscript.regexp")
re.Pattern = "^(\S+)\s?(\S*)\s([^,]+)[,\s]*(.*)$"

For Each c In rg
Range(c.Offset(0, 1), c.Offset(0, 4)).ClearContents
s = c.Value
If re.test(s) Then
Set mc = re.Execute(s)
c.Offset(0, 1).Value = mc(0).submatches(2)
c.Offset(0, 2).Value = mc(0).submatches(0)
c.Offset(0, 3).Value = mc(0).submatches(1)
c.Offset(0, 4).Value = mc(0).submatches(3)
End If
Next c
End Sub
====================================
--ron
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Separating Full Names into First Middle and Last [email protected] Excel Discussion (Misc queries) 1 September 8th 07 02:06 AM
seperate a full name into first, middle, last, & suffix column ichihina Excel Worksheet Functions 1 February 19th 07 05:51 PM
Instead of Parsing Krish Excel Discussion (Misc queries) 1 November 4th 06 08:32 PM
Formulas for Parsing Full names jonefer Excel Worksheet Functions 3 February 14th 06 06:28 AM
Remove middle initial from "first name middle initial" Justin F. Excel Discussion (Misc queries) 15 September 26th 05 06:13 PM


All times are GMT +1. The time now is 03:27 PM.

Powered by vBulletin® Copyright ©2000 - 2023, Jelsoft Enterprises Ltd.
Copyright 2004-2023 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"