Need Excel formula to separate names into columns, with exceptions

[Guest]

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

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

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

 

[Guest]

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]

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