Here is a one-liner UDF that does what the OP asked...

Function GetWholeName(Text As String) As String
GetWholeName = Mid(Text, InStrRev(Text, " ", InStrRev(Text, ",")) + 1)
End Function

It returns a #VALUE! error if the cell's text doesn't have a comma in it.
Your UDF returns the entire cell's text for that condition. Personally, I
might favor returning the empty string if a comma is not present; this UDF
will do that...

Function GetWholeName(Text As String) As String
If InStr(Text, ",") Then GetWholeName = _
Mid(Text, InStrRev(Text, " ", InStrRev(Text, ",")) + 1)
End Function

Rick


"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message
...
How about a user defined function (UDF)? Just copy the code below and
insert
it into a regular code module: open the VB Editor using [Alt]+[F11], then
choose Insert | Module from its menu and copy and past the code below into
it
--

Public Function GetWholeName(cellText As String) As String
Dim workingText As String
Dim firstNameOnly As String

On Error GoTo 0
firstNameOnly = Right(cellText, Len(cellText) - _
InStrRev(cellText, ","))
workingText = Left(cellText, _
Len(cellText) - Len(firstNameOnly))
GetWholeName = Right(workingText, Len(workingText) - _
InStrRev(workingText, " ")) & firstNameOnly
If Err < 0 Then
GetWholeName = ""
Err.Clear
End If
On Error GoTo 0
End Function


To use the function on a worksheet, use formula like this:
=getwholename(A1)
that assumes that your sample text is in cell A1. Change A1 in the
formula
to the cell(s) that contain the text to be parsed.


If, for some reason you need to separate out just the last name, then this
UDF will do that for you:

Public Function GetLastName(cellText As String) As String
On Error Resume Next
GetLastName = Right(Left(cellText, InStrRev(cellText, ",") - 1), _
Len(Left(cellText, InStrRev(cellText, ",") - 1)) - _
InStrRev(Left(cellText, InStrRev(cellText, ",") - 1), " "))
If Err < 0 Then
GetLastName = ""
Err.Clear
End If
On Error GoTo 0
End Function

" wrote:

sample field:
HYDROXYZINE PAM TYA** 2 DOE, JOHN

sample output:
DOE, JOHN

I would like to pull out the name from the field above. Cutting point
should be at the nearest space preceding the last comma.

I'm having trouble wrapping my brain around it. I'd prefer to do it
with functions rather than VB script, but will be glad with either
solution.

Your help is very appreciated.

--T