CODEWITHSUNDEEP
excelsplit
KitKat
KitKat

Reputation: 191

How to separate last three numbers and a string from the rest of the text in excel

I have:

Column 1

Here is line one abc 1 3 100
Here is another line jkmr 5-20 230 3
Other line three rjleer 44 10 22

and want to turn it into:

enter image description here

So far I have this:

=RIGHT(A1,LEN(C1)-FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-3)))

that separates the last four strings. Then I can separate them on spaces. But how can I remove these last four strings from from the text?

Upvotes: 0

Views: 43

Answers (1)

Gary's Student
Gary's Student

Reputation: 96773

This appears to give you the desired result:

Sub BreakUp()
    Dim N As Long, i As Long, k As Long, j As Long
    N = Cells(Rows.Count, "A").End(xlUp).Row

    For i = 1 To N
        k = 0
        arr = Split(Cells(i, 1).Value, " ")

        For j = 6 To 3 Step -1
            Cells(i, j).Value = arr(UBound(arr) - k)
            k = k + 1
        Next j

        For j = 0 To UBound(arr) - 4
            Cells(i, 2).Value = Cells(i, 2).Value & " " & arr(j)
        Next j
    Next i
    End Sub

Upvotes: 1

Related Questions