 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / March 2008Extract first names, middle names, last names and JR/SR
I have formulas which will extract first, middle and last names but I need one that will append the JR/SR/III onto the middle name. What I have now assumes it is the last name. Thanks for your help. Post formulas and examples of your data and required results  SignatureKind regards, Niek Otten Microsoft MVP - Excel |I have formulas which will extract first, middle and last names but I | need one that will append the JR/SR/III onto the middle name. What I | have now assumes it is the last name. | | Thanks for your help. > Post formulas and examples of your data and required results > [quoted text clipped - 9 lines] > | > | Thanks for your help. My data looks like this: (single cell): James P. Smith Jr and I want it to be Smith, James P Jr. Thanks. These are my formulas: =LEFT(A40,FIND(" ",A40)-1) =RIGHT(A40,LEN(A40)-FIND("*",substitute(A40," ","*",LEN(A40)- LEN(SUBSTITUTE(A40," ",""))))) =IF(ISERR(MID(A40,FIND(" ",A40)+1,IF(ISERR(FIND(" ",A40,FIND(" ",A40)+1)),FIND(" ",A40),FIND(" ",A40,FIND(" ",A40)+1))-FIND(" ",A40)-1)),"",MID(A40,FIND(" ",A40)+1,IF(ISERR(FIND(" ",A40,FIND(" ",A40)+1)),FIND(" ",A40),FIND(" ",A40,FIND(" ",A40)+1))-FIND(" ",A40)-1)) >My data looks like this: (single cell): James P. Smith Jr and I want >it to be Smith, James P Jr. Thanks. [quoted text clipped - 11 lines] >",A40)+1)),FIND(" ",A40),FIND(" ",A40,FIND(" ",A40)+1))-FIND(" >",A40)-1)) If all of your data looks exactly like your example (e.g. FirstName MiddleInitial LastName Suffix, then you can use this formula (with the name in A1): =MID(A1,1+FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),2)),-1+ FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),3))-FIND(CHAR(1), SUBSTITUTE(A1," ",CHAR(1),2)))&", "&LEFT(A1,FIND(" ",A1))& MID(A1,FIND(" ",A1),FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),2))- FIND(" ",A1)+1)& MID(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),3))+1,255) But there are a variety of ways that names can be entered. And if you have names entered with formats other than the way you describe it above, things can get very complicated. For example, do all of your names have suffixes? Do they all have middle initials or names? Do any have multiple middle initials or names? If there is a middle name entered, do you want that returned or just the MI? Is there no punctuation other than a "." following a middle initial? Any hyphenated last names? Any with prefixes? etc. Rearranging the names can be done, but if the patterns are any more complicated than the one you show, you will need to define them for us. --ron > >My data looks like this: (single cell): James P. Smith Jr and I want > >it to be Smith, James P Jr. Thanks. [quoted text clipped - 47 lines] > > - Show quoted text - Sorry, I should have been more specific. 1) No prefixes 2) Some w/first and last name only 3) Some middle names 4) Some beginning initials 5) Some middle initials 6) No punctuation except after initials 7) Some names with JR/SR/III Thanks. >> >My data looks like this: (single cell): James P. Smith Jr and I want >> >it to be Smith, James P Jr. Thanks. [quoted text clipped - 57 lines] >7) Some names with JR/SR/III >Thanks. OK, that's more clear. I wrote a UDF that takes the entry in the formats you have specified, and outputs it they way you described earlier. Here is what I used for a sample: James P. Smith Jr James Smith James Smith III James Papa Smith Sr James Smith Sr Here are the results of this function: Smith, James P. Jr Smith, James Smith, James III Smith, James Papa Sr Smith, James Sr If there are more variations, or you need different output, let me know. To set up this function, <alt-F11> opens the VBEditor. Ensure your project is highlighted in the Project Explorer window, then Insert/Module and paste the code below into the window that opens. To use this, enter the function =ParseName(cell_ref) into any cell, where cell_ref refers to the location of your unprocessed string. ========================================= Option Explicit Function ParseName(str As String) As String Dim re As Object Set re = CreateObject("vbscript.regexp") re.IgnoreCase = True re.Global = True re.Pattern = "(^\s*\w+)\s*(([\w.]*?)\s*)?(\w+)\s*(JR|SR|III|$)" ParseName = re.Replace(str, "$4, $1 $3 $5") ParseName = Application.WorksheetFunction.Trim(ParseName) End Function ==================================== --ron > OK, that's more clear. I wrote a UDF that takes the entry in the formats > you [quoted text clipped - 39 lines] > End Function > ==================================== I had a co-worker before I retired whose name was Frank Della Rossa, Della Rossa was his last name. I'm guessing there is no way to handle a name like his as there is no outward sign that the Della is not a middle name. Rick On Thu, 6 Mar 2008 20:52:14 -0500, "Rick Rothstein \(MVP - VB\)" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote: >I had a co-worker before I retired whose name was Frank Della Rossa, Della >Rossa was his last name. I'm guessing there is no way to handle a name like >his as there is no outward sign that the Della is not a middle name. > >Rick I'm no expert in this area, but I think the only way to handle that sort of issue is with a lookup table. And even then, I suppose there could be a Frank Rossa around who just happened to have Della as his middle name! My wife has five names plus a preposition. But the first is still the first name, and the last -- the last name. --ron >I have formulas which will extract first, middle and last names but I >need one that will append the JR/SR/III onto the middle name. What I >have now assumes it is the last name. > >Thanks for your help. Examples of your data, and desired results, would be helpful. --ron
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.