Last Name First pt2

[snax500]

Earlier, I posted the following question and thanks for the great
responses:


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?

Maybe, I wasn't clear but I do not want a formula or a function. I want
a macro to go through my original list and change the name from first
last to last,first. I already have list of names and just want to do a
conversion on the selected cells. I think I would be using InStr or
some other string functions in Visual Basic.

Thanks again.





Thanks

 

[Dave Peterson]

You could use Jim Thomlinson's function in your subroutine:

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

Sub testme()
Dim myRng As Range
Dim myCell As Range

Set myRng = Selection
For Each myCell In myRng.Cells
myCell.Value = FormatName(myCell.Value)
Next myCell
End Sub

Select your range and run the Sub.

 

[Tom Ogilvy]

Jim gave you VBA code:


Sub ChangeOrder()
Dim cell as Range
for each cell in Selection
cell.Value = FormatName(Cell.Text)
Next
end Sub



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

 

[Guest]

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)...
<snip>
I think I would be using InStr or
some other string functions in Visual Basic.

If you use the InStrRev function, you can find the location of the last
space in the string. You would want to Trim() the string before you use
InStrRev to avoid finding a trailing space.

Select the first cell you want to convert, then run something like the
following macro:

Sub FormatNames()
Dim LastSpace As Long
Dim LastName As String
Dim RestOfName As String
Dim Name As String

Name = Trim(ActiveCell.Value)
While Name <> ""
LastSpace = InStrRev(Name, " ")
LastName = Mid(Name, LastSpace + 1)
RestOfName = Mid(Name, 1, LastSpace - 1)
ActiveCell.Value = LastName & ", " & RestOfName
ActiveCell.Offset(1, 0).Select
Name = Trim(ActiveCell.Value)
Wend
End Sub

--Shawn