Extract Numbers and Text from Single Cell with Multiple Numbers & Text

[JacksonTriggs]

Hi all!

Despite searching for similar posts, I have not found a solution to the following problem regarding a single cell with a very long string of multiple numbers and text.
Here is a part of it: 118 A&E 131 AMC 184 Animal Planet....

How can I extract this string into rows with two columns as follows please?

118	A&E
131	AMC
184	Animal Planet

I tried Text to Columns with a space delimiter, but that ended up splitting "Animal" from "Planet" too, so no luck there.

Thanks in advance! Respect!

 

[rlv01]

... with a very long string of multiple numbers and text.
Here is a part of it: 118 A&E 131 AMC 184 Animal Planet....

As long as the string format conforms to the example you posted, something like this will do it. The example assumes your single cell data is in cell A1

Sub SplitToColumns()
    Dim I As Long, J As Long
    Dim S As String, CellData As String
    Dim SA As Variant
   
    CellData = Application.Trim(Range("A1").Value)
    'CellData = "118 A&E 131 AMC 184 Animal Planet"
   
    SA = Split(CellData, " ")
    J = 1
    For I = LBound(SA) To UBound(SA)
        If VBA.IsNumeric(SA(I)) Then
            S = ""
            J = J + 1
            Cells(J, 1) = SA(I)
        Else
            S = S & SA(I) & " "
            Cells(J, 2) = Trim(S)
        End If
    Next I
End Sub

 

[JacksonTriggs]

Wow, @rlv01, did you just whip up that fantastic and perfectly executing macro on the spot? I'm amazed either way. Thank you so very much!

 

[rlv01]

Glad it worked for you.