vba to reorder last,first name in same cell

[lwebbie]

I have a spreadsheet that the names are formatted last,first (starting in cell A2) but I need the order changed. I know the excel formula but that requires me to enter it into an empty cell. Is it possible in VBA to have it switch the order (first last) in the same cell (A2) so it just changes the cell value rather than having to apply the formula to a different cell and then override the data in column A?

Excel formula: MID(A2&" "&A2,FIND(",",A2)+1,LEN(A2))

 

[Peter_SSs]

Try this with a copy of your data.

Sub SwapNames()
  With Range("A2", Range("A" & Rows.Count).End(xlUp))
    .Value = Evaluate(Replace("if(#="""","""",mid(#&"" ""&#,find("","",#)+1,len(#)))", "#", .Address))
  End With
End Sub

 

[lwebbie]

Peter it almost worked but now there is an extra space at the start of the name. Is there a way to fix so I don't also have to do a TRIM?

 

[Haluk_]

An alternative;

Sub Test()    NoA = Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To NoA
        Range("A" & i) = Trim(StrReverse(Split(StrReverse(Range("A" & i)), ",")(0)) & ", " _
                       & StrReverse(Split(StrReverse(Range("A" & i)), ",")(1)))
    Next
End Sub

 

[lwebbie]

this switches the order but what I need is it to remove the , so it would go from Doe, Jane to Jane Doe

 

[lwebbie]

Peter, never mind this worked perfectly! I did it in a practice workbook first and forgot my name are formatted without a space. Reran it and it worked like a charm. THANK YOU!

 

[Peter_SSs]

Peter, never mind this worked perfectly! I did it in a practice workbook first and forgot my name are formatted without a space. Reran it and it worked like a charm. THANK YOU!

No worries, I did exactly the same thing when I tested your original formula.

 

[Haluk_]

this switches the order but what I need is it to remove the , so it would go from Doe, Jane to Jane Doe

You could easily remove the comma sign from my code, which comes as;

Sub Test()NoA = Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To NoA
        Range("A" & i) = Trim(StrReverse(Split(StrReverse(Range("A" & i)), ",")(0)) & " " _
                       & StrReverse(Split(StrReverse(Range("A" & i)), ",")(1)))
    Next
End Sub

 

[Peter_SSs]

You could easily remove the comma sign from my code, which comes as;

Sub Test()NoA = Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To NoA
        Range("A" & i) = Trim(StrReverse(Split(StrReverse(Range("A" & i)), ",")(0)) & " " _
                       & StrReverse(Split(StrReverse(Range("A" & i)), ",")(1)))
    Next
End Sub

Notes:
1. This code will error if there are any blank cells in the range (or any cells not containing a comma)
2. If a looping method was desired over the "whole column at once suggestion in post #2 , then a much simpler string manipulation (including a check for note 1) would be:

Sub Test2()
  NoA = Range("A" & Rows.Count).End(xlUp).Row
  For i = 2 To NoA
    With Range("A" & i)
      If InStr(1, .Value, ",") > 0 Then .Value = Split(.Value & " " & .Value, ",")(1)
    End With
  Next
End Sub