Home |
Search |
Today's Posts |
|
#1
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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 |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Separating Full Names into First Middle and Last | Excel Discussion (Misc queries) | |||
seperate a full name into first, middle, last, & suffix column | Excel Worksheet Functions | |||
Instead of Parsing | Excel Discussion (Misc queries) | |||
Formulas for Parsing Full names | Excel Worksheet Functions | |||
Remove middle initial from "first name middle initial" | Excel Discussion (Misc queries) |