Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Last name first first name last
I don't know if this can be done or not but I'm wanting to make a list of
names in one cell such as Josh Smith but instead of typing the last name first like Smith Josh I would rather type Josh Smith and format the cell somehow to display Smith Josh. Can this be done? Thanks in advance. |
#2
|
|||
|
|||
Private Sub Worksheet_Change(ByVal Target As Range)
Dim iPos As Long On Error GoTo ws_exit: Application.EnableEvents = False With Target If .Column = 1 Then iPos = InStr(1, Target.Value, " ") If iPos 0 Then .Value = Right(.Value, Len(.Value) - iPos) & _ " " & Left(.Value, iPos - 1) End If End If End With ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH RP (remove nothere from the email address if mailing direct) "jtvich" wrote in message ... I don't know if this can be done or not but I'm wanting to make a list of names in one cell such as Josh Smith but instead of typing the last name first like Smith Josh I would rather type Josh Smith and format the cell somehow to display Smith Josh. Can this be done? Thanks in advance. |
#3
|
|||
|
|||
Might I suggest that converting "Josh Smith" to "Smith, Josh"
with a comma would be less ambiguous, AND you would not have to worry about macros reentering data, or use of F2 then Enter. Modify Bob Phillips' earlier code in this thread to test for a comma. If there is a comma present there would be no interest to change. If also means you can later manually change an entry to "Smith, G. Josh".. without it getting messed up. Also since the change only takes place if there is no comma, and this is specifically for people's names you will be able to type in a name in all lower case like "josh smith" but provide correct capitalization if needed for names like "Ron de Bruin" or "Angus McDuff" Private Sub Worksheet_Change(ByVal Target As Range) Dim iPos As Long On Error GoTo ws_exit: Application.EnableEvents = False With Target If .Column = 1 Then If InStr(.Value, ",") = 0 Then '-- test for comma iPos = InStr(1, Target.Value, " ") If iPos 0 Then .Value = Right(.Value, Len(.Value) - iPos) & _ ", " & Left(.Value, iPos - 1) End If If LCase(.Value) = .Value Then .Value = Application.Proper(.Value) End If '--added from test for comma End If End With ws_exit: Application.EnableEvents = True End Sub Wasn't mentioned but this is an Event Macro is installed differently than standard macros Right click on worksheet name, View code, insert the above code. Notice that Bob has a recovery for an on error condition for Application.EnableEvents if you mess up this type of macro and have turned off events then a macro like this would no longer work. You can fix that by typing that line into the Intermediate Window in the Visual Basic Editor. Application.EnableEvents = True more information on Event macros and specifically to reenable after error http://www.mvps.org/dmcritchie/excel/event.htm#problems Another way of doing this would be to use a standard macro invoked manually that does the check for a comma and makes the change if there is not commas. There are lots of examples for this. (probably everyone with an Excel page) http://www.mvps.org/dmcritchie/excel/join.htm#lastname http://www.cpearson.com/excel/FirstLast.htm Please include your name either with your email address or in your signature line. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "jtvich" wrote in ... I don't know if this can be done or not but I'm wanting to make a list of names in one cell such as Josh Smith but instead of typing the last name first like Smith Josh I would rather type Josh Smith and format the cell somehow to display Smith Josh. Can this be done? Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|