Last name first first name last

[Guest]

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.

 

[Bob Phillips]

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)

 

[David McRitchie]

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.