Change names in excel cells




I have a report that is exported from another system that looks like this: 



But I want to figure out a way to have the names in the first column look like this without manually changing them each time I pull the report:

I'm sure this will be pretty easy for this group - but any ideas?  I was hoping for some sort of formula or any other ideas. 


After this, I need to format the rest of the report and am planning to build a Macro to do the rest of the formatting I have planned...




5 Replies


Here is a macro:

Sub ExtractNames()
    Range(Range("A2"), Range("A2").End(xlDown)).TextToColumns _
        destination:=Range("A2"), _
        Other:=True, OtherChar:="(", _
        FieldInfo:=Array(Array(1, xlGeneralFormat), Array(2, xlSkipColumn))
End Sub

Thank you so much for the response. Perhaps I did something wrong, but I tried this and it only removed the number when I ran it, but left the names as:
Smith, John

Is there a way to have them switched to John Smith?

@Hans Vogelaar 


Here is what it looks like in VBA



I'm sorry, I forgot to change the order of first name and last name.

Sub ExtractNames()
    Dim rng As Range
    Dim s As String
    Dim a() As String
    Application.ScreenUpdating = False
    For Each rng In Range(Range("A2"), Range("A2").End(xlDown))
        s = rng.Value
        s = Left(s, InStr(s, "(") - 2)
        a = Split(s, ", ")
        rng.Value = a(1) & " " & a(0)
    Next rng
    Application.ScreenUpdating = True
End Sub
best response confirmed by NeilKloster (Contributor)
@hans - you continue to amaze me! Thank you so much that worked perfectly!!