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