Getting data with web query and rearrange the data

[mrmmickle1]

This code will work....

Please note that the IsNumber Function can be credited to Rick Rothstein.

Sub Test()

    Dim rng As Range
    Dim cll As Range
    Dim IntLp As Integer
    Dim strt As Integer
    
    Set rng = Sheets(1).Range("A1:M29")

    For Each cll In rng
        CllLen = Len(cll)
        If CllLen > 0 Then
            For IntLp = 1 To CllLen
                strt = strt + 1
                If IsNumber(Mid(cll, strt, IntLp)) <> True Then
                    cll.Value = Replace(cll.Value, Mid(cll, strt, IntLp), "")
                End If
            strt = 0
            Next IntLp
        End If
    Next cll

End Sub

Function IsNumber(ByVal Value As String) As Boolean
 ' Uncomment the next statement out if you
 ' want to provide for plus/minus signs
 ' If Value Like "[+-]*" Then Value = Mid$(Value, 2)
 IsNumber = Not Value Like "*[!0-9.]*" And _
 Not Value Like "*.*.*" And _
 Len(Value) > 0 And Value <> "." And _
 Value <> vbNullString
End Function