Flip LASTNAME and Firstname where LASTNAME is always capitalized

[strooman]

Data in column A. I want to split the data to 3 columns and flip the lastname with firstname. Lastname is ALWAYS capitalized.
When firstname or/and lastname consists of 2 words then it can be in 1 cell. Look for example (2 BERNAL Egan Arley) beneath. Any help is appreciated.

Data to change:

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][/tr][tr][td]

1​

[/td][td]Names[/td][/tr]
[tr][td]

2​

[/td][td]1 FROOME Christopher[/td][/tr]
[tr][td]

3​

[/td][td]2 BERNAL Egan Arley[/td][/tr]
[tr][td]

4​

[/td][td]3 CASTROVIEJO Jonathan[/td][/tr]
[tr][td]

5​

[/td][td]4 KWIATKOWSKI Michal[/td][/tr]
[tr][td]

6​

[/td][td]5 MOSCON Gianni[/td][/tr]
[tr][td]

7​

[/td][td]6 POELS Wout[/td][/tr]
[tr][td]

8​

[/td][td]7 ROWE Luke[/td][/tr]
[tr][td]

9​

[/td][td]8 THOMAS Geraint[/td][/tr]
[/table]

Result should be:

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][/tr][tr][td]

1​

[/td][td]Id[/td][td]Firstname[/td][td]Lastname[/td][/tr]
[tr][td]

2​

[/td][td]

1​

[/td][td]Christopher[/td][td]Froome[/td][/tr]
[tr][td]

3​

[/td][td]

2​

[/td][td]Egan Arley[/td][td]Bernal[/td][/tr]
[tr][td]

4​

[/td][td]

3​

[/td][td]Jonathan[/td][td]Castroveijo[/td][/tr]
[tr][td]

5​

[/td][td]

4​

[/td][td]Michal[/td][td]Kwiatkowski[/td][/tr]
[tr][td]

6​

[/td][td]

5​

[/td][td]Gianni[/td][td]Moscon[/td][/tr]
[tr][td]

7​

[/td][td]

6​

[/td][td]Wout[/td][td]Pouls[/td][/tr]
[tr][td]

8​

[/td][td]

7​

[/td][td]Luke[/td][td]Rowe[/td][/tr]
[tr][td]

9​

[/td][td]

8​

[/td][td]Geraint[/td][td]Thomas[/td][/tr]
[/table]

 

[PhilS2520]

Hi stromman,

Look into: To Text to Columns on the Data ribbon,

Look into: Proper on the Home Ribbon, or as a worksheet function in the text group.

 

[mumps]

Try this macro. The data will be output to Sheet2.

Sub SplitData()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("Sheet1").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim rName As Range, splitName As Variant, i As Long, sString As String
    For Each rName In Sheets("Sheet1").Range("A2:A" & LastRow)
        splitName = Split(rName, " ")
        Sheets("Sheet2").Range("A1:C1") = Array("ID", "Firstname", "Lastname")
        Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) = splitName(0)
        For i = 2 To UBound(splitName)
            sString = sString & " " & splitName(i)
        Next i
        Sheets("Sheet2").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0) = Trim(sString)
        Sheets("Sheet2").Cells(Rows.Count, "C").End(xlUp).Offset(1, 0) = splitName(1)
        sString = ""
    Next rName
    Application.ScreenUpdating = True
End Sub

 

[strooman]

Excuses for my late reply. Work was calling.

Thank you everybody for the input. I used all mentioned methods and combined them a little bit.
I adjusted the solution from Mumps here and there. At first I got an error "Subscript out of range". This had to do with the Split function and the divider " " (a space). I had to work a little on this and it appeared to be a "Non breaking space", chr(160) and not a normal "space", chr(32). So I had to adjust that.

splitName = Split(rName, Chr(160))

To put the data to the sheet I tweaked it for my needs.

Option Explicit
Sub SplitData()
Dim rName As Range, splitName As Variant, i As Long, LastRow As Long, rngRow As Long
    
    Application.ScreenUpdating = False
    LastRow = Sheets("Sheet4").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    rngRow = 2
    For Each rName In Sheets("Sheet4").Range("A2:A" & LastRow)
        splitName = Split(rName, Chr(160))
        
        For i = 0 To UBound(splitName)
            Sheets("Sheet5").Cells(rngRow, i + 1) = splitName(i)
        Next i
        
        rngRow = rngRow + 1
    Next rName
    Application.ScreenUpdating = True
End Sub

I want to thank everybody for putting time in this.

 

[mumps]

Glad we could help.