You are correct... Here is the corrected version...

Public Function FormatName(ByVal InputName As String) As String
FormatName = Right(InputName, Len(InputName) - InStrRev(Trim(InputName),
" ")) & _
", " & Trim(Left(InputName, InStrRev(InputName, " ")))
End Function

Sorry...

"Jim at Eagle" wrote:

I lose the middle initial with your function

"Jim Thomlinson" wrote:

Try this function

Public Function FormatName(ByVal InputName As String) As String
FormatName = Right(InputName, Len(InputName) - InStrRev(Trim(InputName),
" ")) & _
", " & Trim(Left(InputName, Len(InputName) - InStrRev(InputName, " ")))
End Function

HTH

"snax500" wrote:

In Excel2000, I have the following data:

John P. Henry
Craig Nelson

I want to use a macro to make the last name first even if there is a
middle initial ( no space between comma and First name). I want it to
look like this:

Henry,John P.
Nelson,Craig

Any ideas?

Thanks