On Fri, 15 May 2009 21:21:01 -0400, Ron Rosenfeld
wrote:

On Fri, 15 May 2009 16:44:02 -0700, kateconrey
wrote:

I have a database with one column of information including names (First Last,
or First Middle Last) and for some names, locations in parenthesis. I am
looking for a function that will allow me to split these pieces into separate
columns for First Name, Middle Name (where applicable), Last Name, and
Location (where listed). Does this exist?

It may be doable, depending on the patterns. You can search the string for the
patterns which define the various substrings you wish to extract.

You'll have to give some examples that include all the variations of what you
want to do.
--ron


Looking at Greg's examples:

First Last
First Middle Last
First Last (Location)
First Middle Last (Location)

IF that is correct, here is another approach with a User Defined Function.

To enter this User Defined Function (UDF), <alt-F11 opens the Visual Basic
Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter these formulas for the
particular portions, assuming that your data is in A2. They should return a
blank for Middle or Location if not present.

First Name: =RegexMid(A2,"(\w+)",1)
Middle Name: =RegexMid(A2,"\w+\s+(\w+)\s+(?!\()",1)
Last Name: =RegexMid(A2,"(\w+$|\w+(?=\s+\())",1)
Location: =RegexMid(A3,"\(([^)]+)",1)


=================================================
Option Explicit
Function RegexMid(str As String, sPattern As String, lSubMatch As Long) As
String
Dim re As Object, mc As Object
Set re = CreateObject("vbscript.regexp")
re.Pattern = sPattern
If re.test(str) = True Then
Set mc = re.Execute(str)
RegexMid = mc(0).submatches(lSubMatch - 1)
End If

End Function
=================================
--ron