Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Name First pt2
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Name First pt2
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. snax500 wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Name First pt2
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 -- regards, Tom Ogilvy "snax500" wrote in message ups.com... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Name First pt2
"snax500" wrote:
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|