On Thu, 6 Mar 2008 06:29:30 -0800 (PST), MrsMrfy <(E-Mail Removed)> wrote:
>On Mar 5, 8:39*pm, Ron Rosenfeld <ronrosenf...@nospam.org> wrote:
>> On Wed, 5 Mar 2008 14:18:30 -0800 (PST), MrsMrfy <MrsM...@gmail.com> wrote:
>> >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))
>>
>> 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 -
>
>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.
>
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
Similar Threads
