Last Name First
 

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

Last Name First
 

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

Last Name First
 

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

Last Name First
 

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

Last Name First
 

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

Last Name First
 

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

Last Name First
 

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

Last Name First
 

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

Last Name First
 

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