On Wed, 21 May 2008 12:06:00 -0700, DZ wrote:

Hi

I want to create three user defined functions
1. FirstName
2. MiddleInitial
3. LastName

so that a user can use them by selecting the function(s) from the Paste
Function Dialog and then simply selecting a cell reference that contains the
full name.

I already know how to create the function arguments as follows

Function FirstName (FulNameCell as range)
Code----------------------
End Function

I need the complete code to get the first name etc

Also

Function Middleinitial (FulNameCell as range)
Code----------------------
End Function

Function LastName (FulNameCell as range)
Code----------------------
End Function

Thanks alot for help with this.

DZ

Here's something that might get you started:

======================================
Option Explicit
Function FirstName(FulNameCell As Range)
FirstName = ParseName(FulNameCell.Value, 0)
End Function
'------------------------------------
Function Middleinitial(FulNameCell As Range)
Middleinitial = ParseName(FulNameCell.Value, 2)
End Function
'------------------------------------
Function LastName(FulNameCell As Range)
LastName = ParseName(FulNameCell.Value, 3)
End Function
'----------------------------------------
Function ParseName(str As String, Part As Long) As String
'Part: 0 = First Name
' 1 = Middle Name
' 2 = Middle Initial
' 3 = Last Name
Dim re As Object, mc As Object, m As Object
Dim i As Long
Set re = CreateObject("vbscript.regexp")
re.Pattern = "^(\S+)\s?((\S?).*)\s(\S+)$"
If re.test(str) Then
Set mc = re.Execute(str)
ParseName = mc(0).submatches(Part)
End If
End Function
================================================== =
--ron