Splitting data from an export interface to their respective columns in excel

[RAM72]

Hi All

I have a file which needs to be split in their respective columns as thy are all merge in one columns

See attached expected results in red .

The data goes up to 2000 rows minimum

 

[jindon]

Try

Sub test()
    Dim a, i As Long, ii As Long
    With Range("a2", Range("a" & Rows.Count).End(xlUp)).Resize(, 4)
        .Offset(1, 1).ClearContents: a = .Value
        With CreateObject("VBScript.RegExp")
            .Pattern = "(.*) Origin[:.] ([A-Z]{3}).* (\d+) *$"
            For i = 2 To UBound(a, 1)
                If .test(a(i, 1)) Then
                    For ii = 2 To 4
                        a(i, ii) = .Replace(a(i, 1), "$" & ii - 1)
                    Next
                End If
            Next
        End With
        .Value = a
    End With
End Sub

 

[RAM72]

Thank you Jindon awesome this piece of code

 

[RAM72]

Try

Sub test()
    Dim a, i As Long, ii As Long
    With Range("a2", Range("a" & Rows.Count).End(xlUp)).Resize(, 4)
        .Offset(1, 1).ClearContents: a = .Value
        With CreateObject("VBScript.RegExp")
            .Pattern = "(.*) Origin[:.] ([A-Z]{3}).* (\d+) *$"
            For i = 2 To UBound(a, 1)
                If .test(a(i, 1)) Then
                    For ii = 2 To 4
                        a(i, ii) = .Replace(a(i, 1), "$" & ii - 1)
                    Next
                End If
            Next
        End With
        .Value = a
    End With
End Sub

Hi Jindon

Some issues
getting some blanks , highlight in yellow

see attached , could you look in what is going wrong..

 

[shrivallabha]

You have specified you need VBA solution but following formula also work for the data posted in the last post.
In B3:
=LEFT(A3,SEARCH(" Origin",A3)-1)
In C3:
=LEFT(TRIM(MID(A3,SEARCH(" Origin",A3)+8,99)),3)
In D3:
=TRIM(RIGHT(SUBSTITUTE(A3," ",REPT(" ",99)),99))

 

[RAM72]

You have specified you need VBA solution but following formula also work for the data posted in the last post.
In B3:
=LEFT(A3,SEARCH(" Origin",A3)-1)
In C3:
=LEFT(TRIM(MID(A3,SEARCH(" Origin",A3)+8,99)),3)
In D3:
=TRIM(RIGHT(SUBSTITUTE(A3," ",REPT(" ",99)),99))

Thank you for formula , works as requested

 

[jindon]

Try change pattern to

            .Pattern = "(.*?) Origin[:.;]? ([A-Z]{3}).* (\d+) *$"