Extract first names, middle names, last names and JR/SR

M

MrsMrfy

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.
 
N

Niek Otten

Post formulas and examples of your data and required results

--
Kind 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.
 
R

Ron Rosenfeld

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.
Click to expand...

Examples of your data, and desired results, would be helpful.
--ron
 
M

MrsMrfy

Post formulas and examples of your data and required results

--
Kind 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.
Click to expand...

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))
 
R

Ron Rosenfeld

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))
Click to expand...

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
 
M

MrsMrfy

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- Hide quoted text -

- Show quoted text -
Click to expand...

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.
 
R

Ron Rosenfeld

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.
Click to expand...

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
 
R

Rick Rothstein \(MVP - VB\)

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
====================================
Click to expand...

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
 
R

Ron Rosenfeld

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
Click to expand...

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Grouping Names in Mail Merge 0
Data in columns 1
Parsing names 8
Mail Merge Help 1
Parsing full name into salutation, First, middle, last, Suffix 1
Possible to select a string in a locked cell? 1
list items from one table in another table based on value in first table 2
Separate Names 3

Top