Last Name First

[snax500]

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

 

[Guest]

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

 

[Guest]

'=CONCATENATE(RIGHT(A1,TRIM(LEN(A1))-IF(ISERR(SEARCH(" ",A1,SEARCH("
",A1)+1)),SEARCH(" ",A1),SEARCH(" ",A1,SEARCH(" ",A1)+1))),",
",LEFT(A1,IF(ISERR(SEARCH(" ",A1,SEARCH(" ",A1)+1)),SEARCH(" ",A1),SEARCH("
",A1,SEARCH(" ",A1)+1))))

this works in 2003

 

[Guest]

I lose the middle initial with your function

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

 

[Guest]

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...

 

[Bob Phillips]

One way

=MID(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))))+1,255)&", "&LEFT(A1,FIND("~",SUBSTITUTE(A1,"
","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1)

--

HTH

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

 

[Steve]

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

 

[Bob Phillips]

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]

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