May 20 2021 12:46 PM
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...
May 20 2021 01:02 PM
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
May 20 2021 02:14 PM
May 20 2021 02:17 PM
May 20 2021 02:21 PM
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
May 20 2021 02:36 PM
SolutionMay 20 2021 02:36 PM
Solution