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??

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

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

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

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