Forum Discussion
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 ...
- hans - you continue to amaze me! Thank you so much that worked perfectly!!
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- 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?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- hans - you continue to amaze me! Thank you so much that worked perfectly!!