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

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

'=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

"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

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

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

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)


"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

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

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

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