make a column from text in macro

Dear All

I have a problem in my office, every day I get a data of movement container that should be put in my system, but the data that I've got should change first in to a column, and there are some character not included.
I usually use a function of "Text to Columns", but it takes more time, split one by one the character that I need

here's the source data sample :

UNA:+.? 'UNB+UNOA:2+TPS+MSC+130706:1101+07061'UNH+07061+CODECO:D:95B:UN:ITG14+SISI021'BGM+34+SISI021201307061192+9'TDT+20+HB327R+1+13+MSC:172:20+++9HA276:103::SIMA SINGAPORE'RFF+VON:HB327R'LOC+9+IDSUB:139:6+TPS:GAT:ZZZ'DTM+178:190012312300:203'DTM+133:190012312300:203'NAD+CF+MSC:160:ZZZ'
EQD+CN+MSCU9283253+4500:102:5++2+5'RFF+BN:411IN1306177'DTM+7:201307061031:203'LOC+7+CNTXG:139:6'LOC+11+SGSIN:139:6+TPS:TER:ZZZ'LOC+147+042054'MEA+AAE+G+KGM:25020'MEA+AAE+T+KGM:4000'MEA+AAE+MW+KGM:30480'SEL+FEJ2888610+CA'TDT+1++3++TPS:172+++L8128UZ:146'NAD+CF+MSC'
EQD+CN+MSCU8206967+4500:102:5++2+4'RFF+BN:'DTM+7:201307060958:203'LOC+7+SGSIN:139:6'LOC+11+SGSIN:139:6+TPS:TER:ZZZ'LOC+147+090022'MEA+AAE+G+KGM:4000'MEA+AAE+T+KGM:4000'MEA+AAE+MW+KGM:30480'TDT+1++3++TPS:172+++L8032UH:146'NAD+CF+MSC'
EQD+CN+MSCU7923670+4500:102:5++2+5'RFF+BN:411IN1306177'DTM+7:201307061043:203'LOC+7+CNTXG:139:6'LOC+11+SGSIN:139:6+TPS:TER:ZZZ'LOC+147+042034'MEA+AAE+G+KGM:23160'MEA+AAE+T+KGM:4000'MEA+AAE+MW+KGM:30480'SEL+FEJ2888607+CA'TDT+1++3++TPS:172+++L9138UF:146'NAD+CF+MSC'

from this data I only need 5 column that contains:
first Column : Container Number (eg. MSCU8206967)
second Column : Container Type (eg. 4500)
third Column : Booking Number (eg. 411IN1306177), sometimes this column is empty because there's no booking number from the source
fourth Column : is a date (eg. 201307061031) we take 8 character from the left as a date with format "yyyymmdd"
fifth Column : is a time (eg. 201307061031) taking 4 character from the right as a time with format "hh:mm"
The result like this

'UNB+UNOA: | #NAME? |61 |061+CODE |CO:D
MSCU9283253 |4500 |411IN1306177 |06/07/2013 |1031
MSCU8206967 |4500 | |06/07/2013 |0958
MSCU7923670 |4500 |411IN1306177 |06/07/2013 |1043

final.jpg

try

Sub test()
    Dim fn As String, x, i As Long, n As Long
    fn = ThisWorkbook.Path & "\IN DATA.txt"  '<-- file path, alter to suite
    With CreateObject("VBScript.RegExp")
        .Pattern = ".*(MSCU\d+)\+(\d+).*BN:(\d+[^']+)?.*DTM\+\d\D+(\d{4})(\d{2})(\d{2})(\d{4}).*"
        If FileLen(fn) Then
            x = Split(CreateObject("Scripting.FileSystemObject").OpenTextFile(fn).ReadAll, vbCrLf)
            For i = 0 To UBound(x)
                If .test(x(i)) Then
                    n = n + 1
                    Cells(n, 1).Resize(, 5).Value = _
                    Split(.Replace(x(i), "$1^$2^$3^$4/$5/$6^$7"), "^")
                End If
            Next
        End If
    End With
End Sub

Dear Jindon

I'm the beginner, what should I do for this formula, should I copy paste, I've tried but there's a message error 53 appeared "file not found" meanwhile the file has already in worksheet.

Dear Jindon

thanks, its work, but what about if I have multiply data, how could I continue it?

Dear Jindon

how to make that bottom?

Could the data continue, with the other name file on the bottom?

Dear All

I've another file again, I've tried jindon's way
Actually there are 27 rows data inside that contain 26 containers

But when I click only 8 data appeared?
is there any fault in it?

Dear Jindon

Thanks, its work
How about like this, I only need copy paste the file into excel, but when I paste into the worksheet, the file automaticly make a column like above? any idea?

You said,

Originally Posted by azbi

but when I paste into the worksheet, the file automaticly make a column like above? any idea?

Run the code after you paste the data in col.A.

Dear Jindon

lets forget it first about, better for me to look you sample file

Run the code after you paste the data in col.A.

Lets look at my question above before it, any idea?

Sub test()
    Dim a, e, x, i As Long
    With CreateObject("VBScript.RegExp")
        .Pattern = ".*\+([^\+]+\d+)\+(\d+).*BN:(\d+[^']+)?.*DTM\+\d\D+(\d{4})(\d{2})(\d{2})(\d{4}).*"
        With Sheets("sheet1")
            x = .Range("a1", .Range("a" & Rows.Count).End(xlUp)).Value
        End With
        For i = 1 To UBound(x)
            If .test(x(i, 1)) Then
                Sheets("sheet2").Range("a" & Rows.Count).End(xlUp)(2).Resize(, 5).Value = _
                Split(.Replace(x(i, 1), "$1^$2^$3^$4/$5/$6^$7"), "^")
            End If
        Next
    End With
    Sheets("sheet2").Columns("a:e").AutoFit
End Sub

Ups...
Its not work, when I put the script in the macros field, then I run it, appeared error mesagge

by the way, you first step above

Quote

When dialog comes up, you can select multiple txt files with holding down Ctrl key.
Attached Files Attached Files
File Type: xls Book1.xls‎ (34.0 KB, 5 views)

Unquote

I just think, change the form like this

first Column : Container Number (eg. MSCU8206967) << the result with a general type
second Column : Container Type (eg. 4500) << the result with a general type
third Column : Booking Number (eg. 411IN1306177), sometimes this column is empty because there's no booking number from the source << the result with a general type
fourth Column : is a date (eg. 201307061031) we take 8 character from the left as a date with format "yyyymmdd" but the result become a format date "yy/mm/yyyy"
fifth Column : is a time (eg. 201307061031) taking 4 character from the right as a time with format "hh:mm"
The result like this << this is fixed, nothing to be changed

'UNB+UNOA: | #NAME? |61 |061+CODE |CO:D
MSCU9283253 |4500 |411IN1306177 |06/07/2013 |1031
MSCU8206967 |4500 | |06/07/2013 |0958
MSCU7923670 |4500 |411IN1306177 |06/07/2013 |1043
and I tried to change, but always error,

Then I'll forget about everything.
Good luck.

Dear Jindon

Thanks a lot for your guidance , I appreciate it

It's still not work, any one could help me?

Its still not work in my side

anyone could help me regard this matter?