Code Request for Names

[mole999]

Any one have a small bit of code that would take a name and swap it around

Select the cell, button press reads the cell value and

John Smith to become SMITH John. Not worried if it doesn't handle more than two bits, have a number to work through, the odd one is not too important in the grand scheme.

 

[Fluff]

How about

Sub mole999()
   Dim St As String, i As Long
   St = ActiveCell.Value
   i = InStrRev(St, " ")
   ActiveCell.Offset(, 1).Value = Right(St, Len(St) - i) & " " & Left(St, i - 1)
End Sub

 

[MickG]

Try:-

[COLOR="Navy"]Sub[/COLOR] MG18Jan30
[COLOR="Navy"]Dim[/COLOR] Sp [COLOR="Navy"]As[/COLOR] Variant, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Txt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
Sp = Split(Selection, " ")
[COLOR="Navy"]For[/COLOR] n = UBound(Sp) To 0 [COLOR="Navy"]Step[/COLOR] -1
    Txt = Txt & IIf(Txt = "", Sp(n), " " & Sp(n))
[COLOR="Navy"]Next[/COLOR] n
Selection = Txt
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[mole999]

Thank you both

 

[Rick Rothstein]

How about

Sub mole999()
   Dim St As String, i As Long
   St = ActiveCell.Value
   i = InStrRev(St, " ")
   ActiveCell.Offset(, 1).Value = [B][COLOR="#FF0000"]UCase([/COLOR][/B]Right(St, Len(St) - i)[B][COLOR="#FF0000"])[/COLOR][/B] & " " & Left(St, i - 1)
End Sub

Try:-

[COLOR="Navy"]Sub[/COLOR] MG18Jan30
[COLOR="Navy"]Dim[/COLOR] Sp [COLOR="Navy"]As[/COLOR] Variant, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Txt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
Sp = Split(Selection, " ")
[COLOR="Navy"]For[/COLOR] n = UBound(Sp) To 0 [COLOR="Navy"]Step[/COLOR] -1
    Txt = Txt & IIf(Txt = "", [B][COLOR="#FF0000"]UCase([/COLOR][/B]Sp(n)[B][COLOR="#FF0000"])[/COLOR][/B], " " & Sp(n))
[COLOR="Navy"]Next[/COLOR] n
Selection = Txt
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

The red additions to Fluff's and Mick's codes above will upper case the last name when moved into the first position (as your original post indicated you wanted).

 

[mole999]

The red additions to Fluff's and Mick's codes above will upper case the last name when moved into the first position (as your original post indicated you wanted).

Thanks Rick

I was after

Sub mole999()
On Error Resume Next
   Dim St As String, i As Long
   St = ActiveCell.Value
   i = InStrRev(St, " ")
   ActiveCell.Offset(, 0).Value = UCase(Right(St, Len(St) - i)) & " " & WorksheetFunction.Proper(Left(St, i - 1))
End Sub

Does what I need

 

[Fluff]

Glad we could help & thanks for the feedback

 

[MickG]

You're welcome