Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need Excel formula to separate names into columns, with exceptions
I'm hoping this is possible..
What I have is a name column like this: ANTHONY D DE ROSE EDUARDA J DE SOSA EMANUEL O DE SOUSA EMANUEL O DE SOUSA JOSHUA W DEAN DALE A DEBRUIN JAMES F DEDIEGO ROBERT COOLIDGE And I need to end up with two columns; a FIRSTNAME and LASTNAME. (FIRSTNAME containing any middle initial and LASTNAME containing any two-part last name such as 'De Rose', etc). Any ideas?? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need Excel formula to separate names into columns, with exceptions
On Fri, 24 Aug 2007 16:36:01 -0700, intuishawn
wrote: I'm hoping this is possible.. What I have is a name column like this: ANTHONY D DE ROSE EDUARDA J DE SOSA EMANUEL O DE SOUSA EMANUEL O DE SOUSA JOSHUA W DEAN DALE A DEBRUIN JAMES F DEDIEGO ROBERT COOLIDGE And I need to end up with two columns; a FIRSTNAME and LASTNAME. (FIRSTNAME containing any middle initial and LASTNAME containing any two-part last name such as 'De Rose', etc). Any ideas?? Given the pattern of names, it can be done using a UDF and Regular Expressions. The assumption is that if there is a single space-delimited single character (optionally followed by a dot) following the first word, that character is a middle initial and everything after is lastname. Otherwise, everything after the first word is the lastname. To enter the UDF, <alt-F11 opens the VB Editor. Ensure your project is highlighted in the Project Explorer window, then Insert/Module and paste the code below into the window that opens. Then you can use the following formulas, assuming your string is in A1: FIRSTNAME: =regexsub(A1,"((^\w+)(\s\w\.?)?)(\s)(.*$)","$1") LASTNAME: =regexsub(A1,"((^\w+)(\s\w\.?)?)(\s)(.*$)","$5") ================================================== ====== Option Explicit Function RegexSub(str As String, sPattern As String, _ sReplace As String) As String Dim re As Object Set re = CreateObject("vbscript.regexp") re.Global = True re.Pattern = sPattern RegexSub = re.Replace(str, sReplace) End Function ================================================== ====== --ron |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need Excel formula to separate names into columns, with exceptions
On Fri, 24 Aug 2007 21:03:51 -0400, Ron Rosenfeld
wrote: On Fri, 24 Aug 2007 16:36:01 -0700, intuishawn wrote: I'm hoping this is possible.. What I have is a name column like this: ANTHONY D DE ROSE EDUARDA J DE SOSA EMANUEL O DE SOUSA EMANUEL O DE SOUSA JOSHUA W DEAN DALE A DEBRUIN JAMES F DEDIEGO ROBERT COOLIDGE And I need to end up with two columns; a FIRSTNAME and LASTNAME. (FIRSTNAME containing any middle initial and LASTNAME containing any two-part last name such as 'De Rose', etc). Any ideas?? Given the pattern of names, it can be done using a UDF and Regular Expressions. The assumption is that if there is a single space-delimited single character (optionally followed by a dot) following the first word, that character is a middle initial and everything after is lastname. Otherwise, everything after the first word is the lastname. To enter the UDF, <alt-F11 opens the VB Editor. Ensure your project is highlighted in the Project Explorer window, then Insert/Module and paste the code below into the window that opens. Then you can use the following formulas, assuming your string is in A1: FIRSTNAME: =regexsub(A1,"((^\w+)(\s\w\.?)?)(\s)(.*$)","$1") LASTNAME: =regexsub(A1,"((^\w+)(\s\w\.?)?)(\s)(.*$)","$5") ================================================= ======= Option Explicit Function RegexSub(str As String, sPattern As String, _ sReplace As String) As String Dim re As Object Set re = CreateObject("vbscript.regexp") re.Global = True re.Pattern = sPattern RegexSub = re.Replace(str, sReplace) End Function ================================================= ======= --ron These formulas might be a bit better; they won't return the optional "." after the MI if it is present, giving you a more standardized result. FIRSTNAME: =regexsub(A1,"((^\w+)(\s\w\b)?)\W+(.*$)","$1") LASTNAME: =regexsub(A1,"((^\w+)(\s\w\b)?)\W+(.*$)","$4") --ron |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need Excel formula to separate names into columns, with except
Ron you are a Godsend! Thank you so much, it worked beautifully! Is there any
way I can send you a gift certificate or something? This will probably get me a raise, so I am very grateful! If you'd like a Starbucks GC or something, email me at intuishawn at yahoo dot com with the email address to send it to. Cheers! -Shawn "Ron Rosenfeld" wrote: On Fri, 24 Aug 2007 21:03:51 -0400, Ron Rosenfeld wrote: On Fri, 24 Aug 2007 16:36:01 -0700, intuishawn wrote: I'm hoping this is possible.. What I have is a name column like this: ANTHONY D DE ROSE EDUARDA J DE SOSA EMANUEL O DE SOUSA EMANUEL O DE SOUSA JOSHUA W DEAN DALE A DEBRUIN JAMES F DEDIEGO ROBERT COOLIDGE And I need to end up with two columns; a FIRSTNAME and LASTNAME. (FIRSTNAME containing any middle initial and LASTNAME containing any two-part last name such as 'De Rose', etc). Any ideas?? Given the pattern of names, it can be done using a UDF and Regular Expressions. The assumption is that if there is a single space-delimited single character (optionally followed by a dot) following the first word, that character is a middle initial and everything after is lastname. Otherwise, everything after the first word is the lastname. To enter the UDF, <alt-F11 opens the VB Editor. Ensure your project is highlighted in the Project Explorer window, then Insert/Module and paste the code below into the window that opens. Then you can use the following formulas, assuming your string is in A1: FIRSTNAME: =regexsub(A1,"((^\w+)(\s\w\.?)?)(\s)(.*$)","$1") LASTNAME: =regexsub(A1,"((^\w+)(\s\w\.?)?)(\s)(.*$)","$5") ================================================= ======= Option Explicit Function RegexSub(str As String, sPattern As String, _ sReplace As String) As String Dim re As Object Set re = CreateObject("vbscript.regexp") re.Global = True re.Pattern = sPattern RegexSub = re.Replace(str, sReplace) End Function ================================================= ======= --ron These formulas might be a bit better; they won't return the optional "." after the MI if it is present, giving you a more standardized result. FIRSTNAME: =regexsub(A1,"((^\w+)(\s\w\b)?)\W+(.*$)","$1") LASTNAME: =regexsub(A1,"((^\w+)(\s\w\b)?)\W+(.*$)","$4") --ron |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need Excel formula to separate names into columns, with except
On Mon, 27 Aug 2007 07:58:00 -0700, intuishawn
wrote: Ron you are a Godsend! Thank you so much, it worked beautifully! Is there any way I can send you a gift certificate or something? This will probably get me a raise, so I am very grateful! If you'd like a Starbucks GC or something, email me at intuishawn at yahoo dot com with the email address to send it to. Cheers! -Shawn Your thanks here is reward enough. Glad to help. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula for counting Exceptions??? | Excel Discussion (Misc queries) | |||
merge a wordfile of names & addresses to an excel separate cells | Excel Worksheet Functions | |||
Excel formula help needed-How to separate last and first names? | Excel Worksheet Functions | |||
I wish to separate city, state, and zip into 3 separate columns | Excel Worksheet Functions | |||
Separate names into 2 columns? | Excel Discussion (Misc queries) |