Aha

Guess who misread the question! How about

Function FixName(myStr)
Dim Temp, i As Integer, x As Integer
Temp = Trim(myStr)
x = 1
For i = Len(Temp) To 1 Step -1
If Mid(Temp, i, 1) = " " Then
x = i + 1
Exit For
End If
Next i
If x = 1 Then
FixName = Temp
Else
FixName = Mid(Temp, x) & ", " & Trim(Left(Temp, x - 1))
End If
End Function





"Bob Phillips" wrote in message
...
Doesn't work. He wants commas in there, there are none to start. Even if
you
change the comma to space, it still doesn't work on John P. Henry.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Steve" wrote in message
...
The non-macro way:
= RIGHT(A1,LEN(A1)-FIND(",",A1) ) & " " & LEFT(A1,FIND(",",A1)-1)

Public Function NameFix(strName As String) As String
NameFix = Right(strName, Len(strName) - InStr(1, strName, ",")) & " " &
Left(strName, InStr(1, strName, ",") - 1)
End Function

HTH
Steve


"snax500" wrote in message
oups.com...
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