Parsing Names in Separate Cells

[dwgnome]

Need assistance on a smart formula (no VBA) that will reverse the order of a list of names - parsing into separate columns.

The formula needs to be able to recognize/distinguish that if a comma(s) is present, then the names are listed as last, first middle. If there is a period this means the name has a middle initial or that a suffix is present. So,if the names are listed as last, first and middle then in the next columns will have in the proper order first name, middle name or initial (keep the period) and last name (loosing the comma of course).

If the original name order is in normal order - first middle and last then the formula should just pass through without any change. Would need to preserve generational titles such as Jr., Sr., III etc. Not sure where the best place for the generational titles should end up. Probably as part of the last name. See examples:

Col A Col B Col C Col D Col E
William J. Clinton William J. Clinton
Churchill, Winston Leonard Spencer Winston Leonard Spencer Churhill
Bates, John John Bates
James Knoll Polk James Knoll Polk
Taft, William H. William H. Taft
Carter, James, Earl, Jr. James Earl Carter Jr.
Rockefeller, John Davidson, IV John Davison Rockefeller IV
Gates, William, H., III William H. Gates III
St. James, Michael, Ken Michael Ken St. James
Cher Cher

I tried using many variations of FIND, SEARCH, LEN and searching all the Excel boards to no avail.

Thank you in advance for any suggestions.

 

[drsarao]

Copy-Paste
Close all workbooks.
Open your workbook (a backup copy preferably).
Right-click on any sheet tab and select "View Code". This will take you to VB editor.
On the left side, there will be a "Project" window. (If not then click on menu "View"-->"Project Explorer".
In the window you will find a folder "Project VBA (yourfile.xls)" with atleast one folder "MS Excel Objects".
Right-click on this folder and select "Insert"-->"Module".
A folder called "Modules" with a subfolder called "Module1" will appear.
Double click on "Module1" and paste the code here.

Run
Go back to MS Excel and select the sheet on which you want to operate the macro.
Click on "Menu"-->"Tools"-->"Macro"-->"Macros".
In the dialog box which appears, select the macro "ParseNames" and then click on the "Run" button.
No Undo after the code runs. So use a backup file first time.
If your file gets trashed after macro runs and you donot have a backup then only recourse is to close the file without saving.
VBA is powerful and versatile. It will be worth learning even if you use Excel minorly.

 

[dwgnome]

Thank you drsarao for the instructions I will try it out later after the Halloween dust settles and let you know if it worked.

 

[dwgnome]

drsarao, your solution worked just as you expected. Bravo!

Is it possible to convert the VBA into a UDF so that I can better control where the names can appear and have the results be automatically updated as I edit or change names?

Thank you and everyone for helping on this name parsing adventure!

 

[drsarao]

UDF is a good idea. However if the names are many, it may slow down your worksheet. In that case VBA is better.
Here goes: (first 2 lines explain usage)

Function RetSeg(nm As String, Optional sgmnt As Integer) As String
'parses name supplied as first argument "nm".
'Segments are returned based on second argument "sgmnt" 1-First, 2-Middle, 3-Last, 4-Suffix, None/0-Full
Dim nms(1 To 1, 1 To 5) '(1-First, 2-Middle, 3-Last, 4-Suffix, 5-Full)
cm = ","  'comma
sp = " "  'space
slen = Len(nm) 'string length
cm1 = InStr(nm, cm) 'first comma
cm2 = InStr(cm1 + 1, nm, cm) 'second
cm3 = IIf(cm2, InStr(cm2 + 1, nm, cm), 0) 'look for comma3 only if comma2 found
sp1 = InStr(nm, sp) 'first space
sp2 = InStr(sp1 + 1, nm, sp) 'second
sp3 = IIf(sp2, InStr(sp2 + 1, nm, sp), 0) 'look for space3 only if space2 found
If cm1 Then 'look for middle initial
    mdi = InStr(sp1, nm, ".") 'period after first space
    If mdi Then If Mid(nm, mdi - 2, 1) = sp Then Else mdi = 0
End If
If cm3 Then
    nms(1, 3) = Left(nm, cm1 - 1)
    nms(1, 1) = Mid(nm, cm1 + 2, cm2 - cm1 - 2)
    nms(1, 2) = Mid(nm, cm2 + 2, cm3 - cm2 - 2)
    nms(1, 4) = Mid(nm, cm3 + 2)
ElseIf cm2 Then
    nms(1, 3) = Left(nm, cm1 - 1)
    nms(1, 1) = Mid(nm, cm1 + 2, cm2 - cm1 - 2)
    nms(1, 2) = Mid(nm, cm2 + 2)
ElseIf cm1 Then
    nms(1, 3) = Left(nm, cm1 - 1)
    If sp3 Then
        nms(1, 1) = Mid(nm, cm1 + 2, sp2 - cm1 - 2)
        If mdi Then
            nms(1, 2) = Mid(nm, sp2 + 1, sp3 - sp2 - 1)
            nms(1, 4) = Mid(nm, sp3 + 1)
        Else
            nms(1, 2) = Mid(nm, sp2 + 1)
        End If
    ElseIf sp2 Then
        nms(1, 1) = Mid(nm, cm1 + 2, sp2 - cm1 - 2)
        nms(1, 2) = Mid(nm, sp2 + 1)
    Else
        nms(1, 1) = Mid(nm, sp1 + 1)
    End If
Else  'no commas at all
    If sp3 Then
        nms(1, 1) = Left(nm, sp1 - 1)
        nms(1, 2) = Mid(nm, sp1 + 1, sp2 - sp1 - 1)
        nms(1, 3) = Mid(nm, sp2 + 1, sp3 - sp2 - 1)
        nms(1, 4) = Mid(nm, sp3 + 1) '4th word is taken as suffix
    ElseIf sp2 Then
        nms(1, 1) = Left(nm, sp1 - 1)
        nms(1, 2) = Mid(nm, sp1 + 1, sp2 - sp1 - 1)
        nms(1, 3) = Mid(nm, sp2 + 1)
    ElseIf sp1 Then
        nms(1, 1) = Left(nm, sp1 - 1)
        nms(1, 3) = Mid(nm, sp1 + 1)
    Else
        nms(1, 1) = nm
    End If
End If
nms(1, 5) = nms(1, 1) & sp & nms(1, 2) & sp & nms(1, 3) & sp & nms(1, 4)
RetSeg = nms(1, IIf(sgmnt, sgmnt, 5))
End Function

You can copy and paste this Function RetSeg in the same module where you put Sub ParseNames.

Sample:

Excel Workbook
	A	B	C	D	E	F
1	Name	First	Middle	Last	Title	Full
2	Bates, John	John		Bates		John Bates
3	Carter, James, Earl, Jr.	James	Earl	Carter	Jr.	James Earl Carter Jr.
4	Cher	Cher				Cher
5	Churchill, Winston Leonard Spencer	Winston	Leonard Spencer	Churchill		Winston Leonard Spencer Churchill
6	Gates, William, H., III	William	H.	Gates	III	William H. Gates III
7	James Knoll Polk	James	Knoll	Polk		James Knoll Polk
8	John Wayne	John		Wayne		John Wayne
9	Rockefeller, John, Davidson, IV	John	Davidson	Rockefeller	IV	John Davidson Rockefeller IV
10	St. James, Michael, Ken	Michael	Ken	St. James		Michael Ken St. James
11	St. James, Michael, Ken, Jr	Michael	Ken	St. James	Jr	Michael Ken St. James Jr
12	Taft, William H.	William	H.	Taft		William H. Taft
13	Taft, William H. Jr.	William	H.	Taft	Jr.	William H. Taft Jr.
14	William J. Clinton	William	J.	Clinton		William J. Clinton
15	William J. Clinton Jr	William	J.	Clinton	Jr	William J. Clinton Jr
16	Winston Leonard Spencer Churchill	Winston	Leonard	Spencer	Churchill	Winston Leonard Spencer Churchill
Sheet8

Excel 2003

Cell Formulas
Range	Formula
B2	=retseg(A2,1)
C2	=retseg(A2,2)
D2	=retseg(A2,3)
E2	=retseg(A2,4)
F2	=retseg(A2)

 

[dwgnome]

Thank you drsarao, that worked fine.

 

[drsarao]

You are welcome.
Let me know when you come across any exceptions.
We can always add another rule.

 

[dougbert]

Hi drsarao,

I realize I'm jumping into this thread pretty late, but I have a request for an additional "rule".

I have used your VBA function and it works well, except...

In a "perfect world" the names in Column A would have perfect punctuation, but when humans enter names, things aren't always so perfect. However, there is one additional check that could be performed that would make this an even better function.

Note that after every occurrence of a comma there SHOULD be a space, but in the real world that doesn't always happen. Would you consider adding additional code near the beginning of your function that would check to see if there's a space following every comma and if there isn't a space following the comma, have the function add the space that SHOULD be there before the function begins parsing the name.

While I wait to see if you respond, I'm going to attempt to see if I can figure out a piece of code that will suffice. However, I suspect you might know how to do this more efficiently since it is your code.

Of course, I'd welcome input from anyone who would like to take a crack at adding this additional functionality to drsarao's code too!

Thanks for any and all efforts!

 

[dougbert]

Update:

In fact, I just discovered 2 more things: 1.) if there isn't a space immediately after the first comma, you will receive an 'invalid procedure call or argument' error on the line:

mdi = InStr(sp1, nm, ".") 'period after first space

when the code would normally begin looking for a period after any middle initial that may exist.

2.) If the person's name in column A has a last name that consists of 2 words (names) separated by a space (e.g.; Di Franco) before the first comma is encountered in a (lastname, firstname) format, you will also receive this same error on the line:

nms(1, 1) = Mid(nm, cm1 + 2, sp2 - cm1 - 2)

These both will present challenges for coding. Put on your "thinking caps"!

 

[drsarao]

Thanks Doug for bringing out the bugs.
However I am not feeling very bright at the moment!
So a quick and dirty solution (more of a workaround actually to prevent error popup)

Insert as the first line (inside) of code:
On Error GoTo ENDIT

And insert at the near end (just before the line ActiveSheet.Range(Cells(i, 2), Cells(i, 6)) = nms in the Macro code and RetSeg = nms(1, IIf(sgmnt, sgmnt, 5)) in the Function code):
ENDIT:

This will ensure that the code carries on even on errors and leaves the segments blank (for later manual entry! Aaargh!).
I did say quick and dirty.

It will be long and arduous job to put in this one rule. I will get around to it one of these rainy days.
Meanwhile, others are invited to try their hand. It is interesting (even challenging)