On Sat, 23 Feb 2008 05:29:01 -0800, CCarter
wrote:

Is there a way for me to change:

Smith, John and Jane

to:

John Smith Jane Smith

Thanks in advance,
Cathy

This should parse names in that general format into adjacent columns. To enter
this macro, <alt-F11 opens the VBEditor. Ensure your project is highlighted
in the project explorer window, then Insert/Module and paste the code below
into the window that opens.

To use this, select the range of cells you wish to process. Ensure there is
nothing valuable in the two adjacent columns as the contents will be erased.
(This behavior can be altered when you specify your requirements more
completely).

<alt-F8 opens the macro dialog box. Select ParseNames and <run

==============================
Sub ParseNames()
Dim c As Range
Dim Str As String
Dim mc As Object
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.IgnoreCase = True
re.Global = True
re.Pattern = "(^[^,]*),\s*((.(?!and))*)(?:\s*and\s*)?(.*$)"
For Each c In Selection
With c
.Offset(0, 1).ClearContents
.Offset(0, 2).ClearContents
Str = .Value
.Offset(0, 1).Value = re.Replace(Str, "$2 $1")
If re.test(Str) = True Then
Set mc = re.Execute(Str)
If Len(mc(0).submatches(3)) 0 Then
.Offset(0, 2).Value = re.Replace(Str, "$4 $1")
End If
End If
End With
Next c
End Sub
=====================================
--ron