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.

 

[dwgnome]

Help would be appreciated

 

[konew1]

You can use Data/Text-to-columns, Use space delimeter and you will split the original data into columns.
You can use =Substitute() function to get rid of the unwanted commas (assuming all commas are unwanted).
That might help a little.

I don't know how you can put the data into the desired "proper" sequence. You will need to identify a pattern of commas, spaces and period marks to work out if each word is a given name or a family name, etc

 

[dwgnome]

Thank you konew1. Text to columns does not get all of the situations and it is not automatic. You are correct in that I would like to rid of the commas upon execution of the formula. Would anyone care to provide an actual formula based solution?

 

[mikerickson]

This is a name Driven solution.

If you have names entered in column A, select B1 and define these Names

Name: nameCell
RefersTo: =Sheet1!$A1

Name: fmlName
RefersTo: =TRIM((MID(nameCell,FIND(",",nameCell&",")+1,255))&" "&LEFT(nameCell,FIND(",",nameCell&",")-1))

Name: firstSpace
RefersTo: =FIND(" ",fmlName&" ")

Name: countOfSpaces
RefersTo: =LEN(fmlName)-LEN(SUBSTITUTE(fmlName," ",""))

Name: lastSpace
RefersTo: =FIND(CHAR(5),SUBSTITUTE(fmlName," ",CHAR(5),MAX(1,countOfSpaces))&CHAR(5))

Name: firstName
RefersTo: =TRIM(LEFT(fmlName,firstSpace))

Name: midName
RefersTo: =TRIM(MID(fmlName,firstSpace,lastSpace-firstSpace))

Name: lastName
RefersTo: =TRIM(MID(fmlName,lastSpace,255))

nameCell is the cell holding a name (parsed either John Q. Smith or Smith, John Q.). Note that the definition is abolute column, relative row.

fmlName returns that name parsed in John Q. Smith format

firstSpace, countOfSpaces and lastSpace are intermediate values.

firstName, midName, and lastName are the results you seek.

If you put =firstName in a cell, it will return the first name of the name entered in column A of that row. Similarly for =midName and =lastName

 

[drsarao]

Excel Workbook
	A	B	C	D
1	Name	Comma1	Comma2
2	William J. Clinton	#VALUE!	#VALUE!	William J. Clinton
3	Churchill, Winston Leonard Spencer	10	#VALUE!	Winston Leonard Spencer Churchill
4	Bates, John	6	#VALUE!	John Bates
5	James Knoll Polk	#VALUE!	#VALUE!	James Knoll Polk
6	Taft, William H.	5	#VALUE!	William H. Taft
7	Carter, James, Earl, Jr.	7	14	James Carter Earl, Jr.
8	Rockefeller, John Davidson, IV	12	27	John Davidson Rockefeller IV
9	Gates, William, H., III	6	15	William Gates H., III
10	St. James, Michael, Ken	10	19	Michael St. James Ken
With couple of helper columns (can be hidden) I could achieve following approximation. As you can see the last 2 names still don't return as you wanted. Even a Comma3 column may not make the formula smarter because data itself is not consistent for comma placement. Maybe you can find some method in the data structure then use a similar formula set.

Excel 2003

Cell Formulas
Range	Formula
B2	=FIND(",",A2)
C2	=FIND(",",A2,B2+1)
D2	=IF(ISERROR(B2),A2,IF(ISERROR(C2),MID(A2,B2+2,100)& " " &LEFT(A2,B2-1),MID(A2,B2+2,C2-B2-2) & " " & LEFT(A2,B2-1) & " " & MID(A2,C2+2,100)))

 

[dwgnome]

Thank you both for providing solutions.

Drsarao your idea in providing a starting place via helper columns is great. It looks like most of what I am needing is there. Just need to get the middle names and generational titles in the proper place. I am hoping that someone can tweak the formula to get the extra comma delimited words (middle names) in the proper place.

Just need to get the desired results:

James Earl Carter, Jr.
William H. Gates, III
Michael Ken St. James

Excel Workbook
	A	B	C	D
1	Name	Comma1	Comma2	Reverse Name
2	William J. Clinton	#VALUE!	#VALUE!	William J. Clinton
3	Churchill, Winston Leonard Spencer	10	#VALUE!	Winston Leonard Spencer Churchill
4	Bates, John	6	#VALUE!	John Bates
5	James Knoll Polk	#VALUE!	#VALUE!	James Knoll Polk
6	Taft, William H.	5	#VALUE!	William H. Taft
7	Carter, James, Earl, Jr.	7	14	James Carter Earl, Jr.
8	Rockefeller, John Davidson, IV	12	27	John Davidson Rockefeller IV
9	Gates, William, H., III	6	15	William Gates H., III
10	St. James, Michael Ken	10	#VALUE!	Michael Ken St. James
11	Cher	#VALUE!	#VALUE!	Cher
Sheet1

mikerickson,

I tried your Named Range approach which is turns out to be an excellent idea, I would not have been able to figure that out. I inputted all of the names as directed. I only changed the $A1 to $A2 since I have a header in my case. The results is shown below.

Excel Workbook
	F	G	H
1	First Name	Middle	Last
2	William	J.	Clinton
3	Winston	Leonard Spencer	Churchill
4	John	*	Bates
5	James	Knoll	Polk
6	William	H.	Taft
7	James,	Earl, Jr.	Carter
8	John	Davidson, IV	Rockefeller
9	William,	H., III	Gates
10	Michael	Ken St.	James
11	Cher	*	*
Sheet1

Excel Workbook
	F
30	countOfSpaces *=LEN(fmlName)-LEN(SUBSTITUTE(fmlName," ",""))
31	firstName *=TRIM(LEFT(fmlName,firstSpace))
32	firstSpace *=FIND(" ",fmlName&" ")
33	fmlName *=TRIM((MID(nameCell,FIND(",",nameCell&",")+1,255))&" "&LEFT(nameCell,FIND(",",nameCell&",")-1))
34	lastName *=TRIM(MID(fmlName,lastSpace,255))
35	lastSpace *=FIND(CHAR(5),SUBSTITUTE(fmlName," ",CHAR(5),MAX(1,countOfSpaces))&CHAR(5))
36	midName *=TRIM(MID(fmlName,firstSpace,lastSpace-firstSpace))
37	nameCell *=Sheet1!$A2
Sheet1

Is there any way to tweak the named range formulas so that the resultant names do not have any commas, that St. James stays together and the generational titles go after the last name. Any help would be appreciated.

 

[mikerickson]

change the definition of fmlName to

=TRIM(SUBSTITUTE((MID(nameCell,FIND(",",nameCell&",")+1,255))&" "&LEFT(nameCell,FIND(",",nameCell&",")-1),","," "))

Basicaly the two approaches are the same.
Each of my name formulas could be put in a helper column, with downstream references made to that column rather than the name.

Similarly, each column of the helper column method could be given a name, hidding all that work.

The advantage of the helper column method is ease of editing the formula.
The name approach takes up less spreadsheet space and looks slicker.

Probably the very slickest approach would be to use the helper column method, testing, testing, testing until the formula are exactly what you want and then folding it into a named structure.

 

[drsarao]

Mickerickson,
Thanks for showing an elegant Names based approach.
Will devote some time exploring it.

 

[Peter_SSs]

dwgnome

Can you confirm whether the result should appear in 3 columns or 1 column? You initially said separate columns but also seem to be encouraging drsarao's approach which puts it in 1.

I think you will have some trouble getting the suffix in with the last name (or in getting the middle name(s) right). How would we determine if

Gates, William I. I. (or Gates, William I., I.)

was really
William Ian Isaac Gates (ie 2 middle, no suffix)
or
William Ian Gates I ? (ie 1 middle 1 suffix)

Here is my formula based attempt so far - using helper columns (which could be hidden).

It basically mirrors Mike's results, except for an improvement (I think) with St. James.

All formulas copied down.

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