Parsing Names in Separate Cells

dwgnome

Active Member
Joined
Dec 18, 2005
Messages
441
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.
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

konew1

Well-known Member
Joined
Oct 17, 2007
Messages
2,287
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

Active Member
Joined
Dec 18, 2005
Messages
441
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,918

ADVERTISEMENT

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
 
Last edited:

drsarao

Well-known Member
Joined
Sep 9, 2009
Messages
1,143
Office Version
  1. 2007
Platform
  1. Windows
Excel Workbook
ABCD
1NameComma1Comma2
2William J. Clinton#VALUE!#VALUE!William J. Clinton
3Churchill, Winston Leonard Spencer10#VALUE!Winston Leonard Spencer Churchill
4Bates, John6#VALUE!John Bates
5James Knoll Polk#VALUE!#VALUE!James Knoll Polk
6Taft, William H.5#VALUE!William H. Taft
7Carter, James, Earl, Jr.714James Carter Earl, Jr.
8Rockefeller, John Davidson, IV1227John Davidson Rockefeller IV
9Gates, William, H., III615William Gates H., III
10St. James, Michael, Ken1019Michael 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
RangeFormula
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

Active Member
Joined
Dec 18, 2005
Messages
441

ADVERTISEMENT

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
ABCD
1NameComma1Comma2Reverse Name
2William J. Clinton#VALUE!#VALUE!William J. Clinton
3Churchill, Winston Leonard Spencer10#VALUE!Winston Leonard Spencer Churchill
4Bates, John6#VALUE!John Bates
5James Knoll Polk#VALUE!#VALUE!James Knoll Polk
6Taft, William H.5#VALUE!William H. Taft
7Carter, James, Earl, Jr.714James Carter Earl, Jr.
8Rockefeller, John Davidson, IV1227John Davidson Rockefeller IV
9Gates, William, H., III615William Gates H., III
10St. James, Michael Ken10#VALUE!Michael Ken St. James
11Cher#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
FGH
1First NameMiddleLast
2WilliamJ.Clinton
3WinstonLeonard SpencerChurchill
4John*Bates
5JamesKnollPolk
6WilliamH.Taft
7James,Earl, Jr.Carter
8JohnDavidson, IVRockefeller
9William,H., IIIGates
10MichaelKen St.James
11Cher**
Sheet1
Excel Workbook
F
30countOfSpaces *=LEN(fmlName)-LEN(SUBSTITUTE(fmlName," ",""))
31firstName *=TRIM(LEFT(fmlName,firstSpace))
32firstSpace *=FIND(" ",fmlName&" ")
33fmlName *=TRIM((MID(nameCell,FIND(",",nameCell&",")+1,255))&" "&LEFT(nameCell,FIND(",",nameCell&",")-1))
34lastName *=TRIM(MID(fmlName,lastSpace,255))
35lastSpace *=FIND(CHAR(5),SUBSTITUTE(fmlName," ",CHAR(5),MAX(1,countOfSpaces))&CHAR(5))
36midName *=TRIM(MID(fmlName,firstSpace,lastSpace-firstSpace))
37nameCell *=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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,918
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

Well-known Member
Joined
Sep 9, 2009
Messages
1,143
Office Version
  1. 2007
Platform
  1. Windows
Mickerickson,
Thanks for showing an elegant Names based approach.
Will devote some time exploring it.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEF
1CommaSpaceFirstMiddleLast
2William J. Clinton08WilliamJ.Clinton
3Churchill, Winston Leonard Spencer1011WinstonLeonard SpencerChurchill
4Bates, John67JohnBates
5James Knoll Polk06JamesKnollPolk
6Taft, William H.56WilliamH.Taft
7Carter, James, Earl, Jr.78JamesEarl, Jr.Carter
8Rockefeller, John Davidson, IV1213JohnDavidson, IVRockefeller
9Gates, William, H., III67WilliamH., IIIGates
10St. James, Michael, Ken104MichaelKenSt. James
11Cher00Cher
12Taft, William H. Jr.56WilliamH. Jr.Taft
13Winston Leonard Spencer Churchill08WinstonLeonard SpencerChurchill
Parse names
 

Watch MrExcel Video

Forum statistics

Threads
1,123,292
Messages
5,600,765
Members
414,405
Latest member
Zaurb

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top