Forum Discussion

NeilKloster's avatar
NeilKloster
Brass Contributor
May 20, 2021
Solved

Change names in excel cells

Hello, 

 

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...

 

 

 

admin
excel
Formulas and Functions
Macros and VBA
office 365
training

5 Replies

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    May 20, 2021

    NeilKloster 

    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
    • NeilKloster's avatar
      NeilKloster
      Brass Contributor
      May 20, 2021
      hans

      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?
      • HansVogelaar's avatar
        HansVogelaar
        MVP
        May 20, 2021

        NeilKloster 

        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

Resources