| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing Long String - String Manipulation (EDI EANCOM 96a)
I have a long text file , some of you may recognise it as a EDI EANCOM 96a
file... Basically, this file is sent via EDI, but is not imported into our systems. It is purely used to get sales figures 'LIN+1++9999999932234:EN'QTY+153:1 each line is prefixed with 'LIN+ 'LOC+162+9999999949625::9 will give me the location However the file is one long string, not CSV etc so i have to find a way to get the imported file to split into rows at each 'LIN+ i have managed to get the text to import, but all in one cell, or split into the world allocation of cells... I would like to be able to get the file to dump into a sheet as follows LOC 9999999949625 LIN STORE QTY 153 QTY 200 QTY 301 1 999999932234 1 2 9999999953277 1 3 9999999956407 1 4 9999999970212 2 Note i also recieve an Inventory File that has a similar format (QTY 200, 301 etc ), so i want to import that at some point to... Once i get the idea on the best wat to split the file with one QTY i will look at the bigger one... As you can see from the text extract below of the file , its not fixed width of very easy to split.... THIS DONT WORK... ________________________________________ Sub testme2() Dim FName As String Dim KeepTheNext As Boolean Dim FNum As Long Dim i As Long Dim sLine As String FName = "C:\SLSRPT.txt" FNum = FreeFile Open FName For Input As FNum i = 1 KeepTheNext = False Do While Not EOF(FNum) Input #FNum, sLine If InStr(1, sLine, "'LIN+", vbTextCompare) 0 Then KeepTheNext = True Else If KeepTheNext = True Then KeepTheNext = False Cells(i, 1).Value = sLine ' Exit Do i = i + 1 End If End If Loop End Sub HELP!!!!! Thanks for any advice in advance Brian ________________________________________ SAMPLE OF TEXT.... UNB+UNOA:3+999999990004:14+9999999900001+060205:05 08+435+ +SLSRPT'UNH+22+SLSRPT:D:96A:UN:EAN008'BGM+73E::9+0 0000014+9'DTM+356:20060129-20060204:718'NAD+FR+9999999900004::9'NAD+SU+999999 9900001::9'LOC+162+9999999949625::9'LIN+1++9999999 932234:EN'QTY+153:1'LIN+2++9999999953277:EN'QTY+15 3:-1'LIN+3++9999999956407:EN'QTY+153:1'LIN+4++9999999 970212:EN'QTY+153:2'LOC+162+5023949057895::9'LIN+5 ++21481505:EN'QTY+153:1'LIN+6++9999999923492:EN'QT Y+153:-1'LIN+7++9999999929029:EN'QTY+153:2'LIN+8++9999999 932418:EN'QTY+153:1'LIN+9++9999999937482:EN'QTY+15 3:1'LIN+10++9999999949560:EN'QTY+153:-1'LIN+11++9999999953253:EN'QTY+153:2'LIN+12++99999 99953888:EN'QTY+153:3'LIN+13++9999999954205:EN'QTY +153:2'LIN+14++9999999954250:EN'QTY+153:1'LIN+15++ 9999999956407:EN'QTY+153:1'LIN+16++9999999957015:E N'QTY+153:7'LIN+17++9999999957022:EN'QTY+153:2'LIN +18++9999999957473:EN'QTY+153:1'LIN+19++9999999957 688:EN'QTY+153:3'LIN+20++9999999957794:EN'QTY+153: 2'LIN+21++9999999970496:EN'QTY+153:3'LOC+162+50239 49136774::9'LIN+22++21326806:EN'QTY+153:1'LIN+23++ 9999999923492:EN'QTY+153:2'LIN+24++9999999929029:E N'QTY+153:1'LIN+25++9999999932418:EN'QTY+153:2'LIN +26++9999999953253:EN'QTY+153:1'LIN+27++9999999953 260:EN'QTY+153:1'LIN+28++9999999953284:EN'QTY+153: 1'LIN+29++9999999953307:EN'QTY+153:1'LIN+30++99999 99953338:EN'QTY+153:3'LIN+31++9999999953345:EN'QTY +153:3'LIN+32++9999999953383:EN'QTY+153:-1'LIN+33++9999999953888:EN'QTY+153:7'LIN+34++99999 99956391:EN'QTY+153:1'LIN+35++9999999956407:EN'QTY +153:1'LIN+36++9999999957015:EN'QTY+153:2'LIN+37++ 9999999957022:EN'QTY+153:1'LIN+38++9999999957183:E N'QTY+153:1'LIN+39++9999999957688:EN'QTY+153:2'LIN +40++9999999957695:EN'QTY+153:1'LIN+41++9999999957 749:EN'QTY+153:7'LIN+42++9999999957794:EN'QTY+153: 10'LIN+43++9999999957879:EN'QTY+153:1'LIN+44++9999 999957985:EN'QTY+153:1'LIN+45++9999999958821:EN'QT Y+153:-1'LIN+46++9999999961616:EN'QTY+153:3'LIN+47++99999 99970199:EN'QTY+153:4'LIN+48++9999999970205:EN'QTY +153:2'LIN+49++9999999970212:EN'QTY+153:5'LIN+50++ 9999999970649:EN'QTY+153:2'LOC+162+5023949145040:: 9'LIN+51++5014838064023:EN'QTY+153:3'LIN+52++99999 99923492:EN'QTY+153:1'LIN+53++9999999929029:EN'QTY +153:1'LIN+54++9999999932234:EN'QTY+153:3'LIN+55++ 9999999938557:EN'QTY+153:1'LIN+56++9999999953253:E N'QTY+153:2'LIN+57++9999999953277:EN'QTY+153:2'LIN +58++9999999953284:EN'QTY+153:1'LIN+59++9999999953 888:EN'QTY+153:3'LIN+60++9999999954281:EN'QTY+153: 1'LIN+61++9999999957015:EN'QTY+153:6'LIN+62++99999 99957022:EN'QTY+153:5'LIN+63++9999999957671:EN'QTY +153:2'LIN+64++9999999957794:EN'QTY+153:-1'LIN+65++9999999958821:EN'QTY+153:2'LIN+66++99999 99970205:EN'QTY+153:1'LOC+162+5023949182579::9'LIN +67++21326806:EN'QTY+153:1'LIN+68++9999999923492:E N'QTY+153:3'LIN+69++9999999929029:EN'QTY+153:1'LIN +70++9999999929951:EN'QTY+153:-1'LIN+71++9999999932234:EN'QTY+153:2'LIN+72++99999 99937482:EN'QTY+153:2'LIN+73++9999999938557:EN'QTY +153:3'LIN+74++9999999943353:EN'QTY+153:1'LIN+75++ 9999999951419:EN'QTY+153:1'LIN+76++9999999952942:E N'QTY+153:2'LIN+77++9999999953079:EN'QTY+153:6'LIN +78++9999999953147:EN'QTY+153:1'LIN+79++9999999953 154:EN'QTY+153:1'LIN+80++9999999953253:EN'QTY+153: 1'LIN+81++9999999953260:EN'QTY+153:4'LIN+82++99999 99953277:EN'QTY+153:1'LIN+83++9999999953307:EN'QTY +153:1'LIN+84++9999999953338:EN'QTY+153:2'LIN+85++ 9999999953352:EN'QTY+153:2'LIN+86++9999999953888:E N'QTY+153:-3'LIN+87++9999999954267:EN'QTY+153:3'LIN+88++99999 99954328:EN'QTY+153:1'LIN+89++9999999956384:EN'QTY +153:5'LIN+90++9999999956391:EN'QTY+153:9'LIN+91++ 9999999956407:EN'QTY+153:3'LIN+92++9999999957015:E N'QTY+153:8'LIN+93++9999999957022:EN'QTY+153:4'LIN +94++9999999957251:EN'QTY+153:-2'LIN+95++9999999957473:EN'QTY+153:1'LIN+96++99999 99957619:EN'QTY+153:1'LIN+97++9999999957657:EN'QTY +153:-2'LIN+98++9999999957695:EN'QTY+153:1'LIN+99++99999 99957732:EN'QTY+153:1'LIN+100++9999999957749:EN'QT Y+153:6'LIN+101++9999999957886:EN'QTY+153:2'LIN+10 2++9999999957985:EN'QTY+153:5'LIN+103++99999999580 05:EN'QTY+153:1'LIN+104++9999999958821:EN'QTY+153: 8'LIN+105++9999999960657:EN'QTY+153:-1'LIN+106++9999999961616:EN'QTY+153:2'LIN+107++999 9999970199:EN'QTY+153:6'LIN+108++9999999970205:EN' QTY+153:5'LIN+109++9999999970557:EN'QTY+153:2'LOC+ 162+5023949223920::9'LIN+110++5014838064023:EN'QTY +153:3'LIN+111++9999999929029:EN'QTY+153:1'LIN+112 ++9999999929951:EN'QTY+153:2'LIN+113++999999992996 8:EN'QTY+153:-1'LIN+114++9999999938557:EN'QTY+153:1'LIN+115++999 9999949560:EN'QTY+153:2'LIN+116++9999999953154:EN' QTY+153:4'LIN+117++9999999953253:EN'QTY+153:3'LIN+ 118++9999999953284:EN'QTY+153:1'LIN+119++999999995 3352:EN'QTY+153:1'LIN+120++9999999953390:EN'QTY+15 3:1'LIN+121++9999999953888:EN'QTY+153:3'LIN+122++9 999999956384:EN'QTY+153:5'LIN+123++9999999956391:E N'QTY+153:14'LIN+124++9999999956407:EN'QTY+153:2'L IN+125++9999999957015:EN'QTY+153:3'LIN+126++999999 9957183:EN'QTY+153:3'LIN+127++9999999957251:EN'QTY +153:2'LIN+128++9999999957671:EN'QTY+153:4'LIN+129 ++9999999957688:EN'QTY+153:2'LIN+130++999999995770 1:EN'QTY+153:-1'LIN+131++9999999957794:EN'QTY+153:2'LIN+132++999 9999957985:EN'QTY+153:1'LIN+133++9999999961616:EN' QTY+153:7'LIN+134++9999999970199:EN'QTY+153:1'LOC+ 162+5023949232199::9'LIN+135++9999999923492:EN'QTY +153:1'LIN+136++9999999951419:EN'QTY+153:2'LIN+137 ++9999999953147:EN'QTY+153:-1'LIN+138++9999999953253:EN'QTY+153:8'LIN+139++999 9999953260:EN'QTY+153:3'LIN+140++9999999953277:EN' QTY+153:2'LIN+141++9999999953345:EN'QTY+153:1'LIN+ 142++9999999953888:EN'QTY+153:2'LIN+143++999999995 6384:EN'QTY+153:3'LIN+144++9999999956391:EN'QTY+15 3:1'LIN+145++9999999956407:EN'QTY+153:1'LIN+146++9 999999957015:EN'QTY+153:1'LIN+147++9999999957022:E N'QTY+153:4'LIN+148++9999999957749:EN'QTY+153:3'LI N+149++9999999957909:EN'QTY+153:2'LIN+150++9999999 957985:EN'QTY+153:2'LIN+151++9999999970540:EN'QTY+ 153:2'UNT+1608+22'UNZ+1+435' |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Importing Long String - String Manipulation (EDI EANCOM 96a)
Here is a start. You would have to tell me what to do with the first line
and what the LOC lines are. Sub testme2() Dim FName As String Dim FNum As Long Dim l As String Dim l1 As Variant FName = "C:\SLSRPT.txt" FNum = FreeFile Open FName For Input As FNum Line Input #FNum, l l1 = Split(l, "LIN+") Cells(1, 1).Resize(UBound(l1) - _ LBound(l1) + 1).Value = Application. _ Transpose(l1) Close #FNum End Sub -- regards, Tom Ogilvy "Brian" wrote in message ... I have a long text file , some of you may recognise it as a EDI EANCOM 96a file... Basically, this file is sent via EDI, but is not imported into our systems. It is purely used to get sales figures 'LIN+1++9999999932234:EN'QTY+153:1 each line is prefixed with 'LIN+ 'LOC+162+9999999949625::9 will give me the location However the file is one long string, not CSV etc so i have to find a way to get the imported file to split into rows at each 'LIN+ i have managed to get the text to import, but all in one cell, or split into the world allocation of cells... I would like to be able to get the file to dump into a sheet as follows LOC 9999999949625 LIN STORE QTY 153 QTY 200 QTY 301 1 999999932234 1 2 9999999953277 1 3 9999999956407 1 4 9999999970212 2 Note i also recieve an Inventory File that has a similar format (QTY 200, 301 etc ), so i want to import that at some point to... Once i get the idea on the best wat to split the file with one QTY i will look at the bigger one... As you can see from the text extract below of the file , its not fixed width of very easy to split.... THIS DONT WORK... ________________________________________ Sub testme2() Dim FName As String Dim KeepTheNext As Boolean Dim FNum As Long Dim i As Long Dim sLine As String FName = "C:\SLSRPT.txt" FNum = FreeFile Open FName For Input As FNum i = 1 KeepTheNext = False Do While Not EOF(FNum) Input #FNum, sLine If InStr(1, sLine, "'LIN+", vbTextCompare) 0 Then KeepTheNext = True Else If KeepTheNext = True Then KeepTheNext = False Cells(i, 1).Value = sLine ' Exit Do i = i + 1 End If End If Loop End Sub HELP!!!!! Thanks for any advice in advance Brian ________________________________________ SAMPLE OF TEXT.... UNB+UNOA:3+999999990004:14+9999999900001+060205:05 08+435+ +SLSRPT'UNH+22+SLSRPT:D:96A:UN:EAN008'BGM+73E::9+0 0000014+9'DTM+356:20060129 -20060204:718'NAD+FR+9999999900004::9'NAD+SU+999999 9900001::9'LOC+162+999999 9949625::9'LIN+1++9999999932234:EN'QTY+153:1'LIN+2 ++9999999953277:EN'QTY+153 :-1'LIN+3++9999999956407:EN'QTY+153:1'LIN+4++9999999 970212:EN'QTY+153:2'LOC+ 162+5023949057895::9'LIN+5++21481505:EN'QTY+153:1' LIN+6++9999999923492:EN'QT Y+153:-1'LIN+7++9999999929029:EN'QTY+153:2'LIN+8++9999999 932418:EN'QTY+153:1 'LIN+9++9999999937482:EN'QTY+153:1'LIN+10++9999999 949560:EN'QTY+153:-1'LIN+1 1++9999999953253:EN'QTY+153:2'LIN+12++999999995388 8:EN'QTY+153:3'LIN+13++999 9999954205:EN'QTY+153:2'LIN+14++9999999954250:EN'Q TY+153:1'LIN+15++999999995 6407:EN'QTY+153:1'LIN+16++9999999957015:EN'QTY+153 :7'LIN+17++9999999957022:E N'QTY+153:2'LIN+18++9999999957473:EN'QTY+153:1'LIN +19++9999999957688:EN'QTY+ 153:3'LIN+20++9999999957794:EN'QTY+153:2'LIN+21++9 999999970496:EN'QTY+153:3' LOC+162+5023949136774::9'LIN+22++21326806:EN'QTY+1 53:1'LIN+23++9999999923492 :EN'QTY+153:2'LIN+24++9999999929029:EN'QTY+153:1'L IN+25++9999999932418:EN'QT Y+153:2'LIN+26++9999999953253:EN'QTY+153:1'LIN+27+ +9999999953260:EN'QTY+153: 1'LIN+28++9999999953284:EN'QTY+153:1'LIN+29++99999 99953307:EN'QTY+153:1'LIN+ 30++9999999953338:EN'QTY+153:3'LIN+31++99999999533 45:EN'QTY+153:3'LIN+32++99 99999953383:EN'QTY+153:-1'LIN+33++9999999953888:EN'QTY+153:7'LIN+34++99999 99 956391:EN'QTY+153:1'LIN+35++9999999956407:EN'QTY+1 53:1'LIN+36++9999999957015 :EN'QTY+153:2'LIN+37++9999999957022:EN'QTY+153:1'L IN+38++9999999957183:EN'QT Y+153:1'LIN+39++9999999957688:EN'QTY+153:2'LIN+40+ +9999999957695:EN'QTY+153: 1'LIN+41++9999999957749:EN'QTY+153:7'LIN+42++99999 99957794:EN'QTY+153:10'LIN +43++9999999957879:EN'QTY+153:1'LIN+44++9999999957 985:EN'QTY+153:1'LIN+45++9 999999958821:EN'QTY+153:-1'LIN+46++9999999961616:EN'QTY+153:3'LIN+47++99999 9 9970199:EN'QTY+153:4'LIN+48++9999999970205:EN'QTY+ 153:2'LIN+49++999999997021 2:EN'QTY+153:5'LIN+50++9999999970649:EN'QTY+153:2' LOC+162+5023949145040::9'L IN+51++5014838064023:EN'QTY+153:3'LIN+52++99999999 23492:EN'QTY+153:1'LIN+53+ +9999999929029:EN'QTY+153:1'LIN+54++9999999932234: EN'QTY+153:3'LIN+55++99999 99938557:EN'QTY+153:1'LIN+56++9999999953253:EN'QTY +153:2'LIN+57++99999999532 77:EN'QTY+153:2'LIN+58++9999999953284:EN'QTY+153:1 'LIN+59++9999999953888:EN' QTY+153:3'LIN+60++9999999954281:EN'QTY+153:1'LIN+6 1++9999999957015:EN'QTY+15 3:6'LIN+62++9999999957022:EN'QTY+153:5'LIN+63++999 9999957671:EN'QTY+153:2'LI N+64++9999999957794:EN'QTY+153:-1'LIN+65++9999999958821:EN'QTY+153:2'LIN+66+ +9999999970205:EN'QTY+153:1'LOC+162+5023949182579: :9'LIN+67++21326806:EN'QTY +153:1'LIN+68++9999999923492:EN'QTY+153:3'LIN+69++ 9999999929029:EN'QTY+153:1 'LIN+70++9999999929951:EN'QTY+153:-1'LIN+71++9999999932234:EN'QTY+153:2'LIN+ 72++9999999937482:EN'QTY+153:2'LIN+73++99999999385 57:EN'QTY+153:3'LIN+74++99 99999943353:EN'QTY+153:1'LIN+75++9999999951419:EN' QTY+153:1'LIN+76++99999999 52942:EN'QTY+153:2'LIN+77++9999999953079:EN'QTY+15 3:6'LIN+78++9999999953147: EN'QTY+153:1'LIN+79++9999999953154:EN'QTY+153:1'LI N+80++9999999953253:EN'QTY +153:1'LIN+81++9999999953260:EN'QTY+153:4'LIN+82++ 9999999953277:EN'QTY+153:1 'LIN+83++9999999953307:EN'QTY+153:1'LIN+84++999999 9953338:EN'QTY+153:2'LIN+8 5++9999999953352:EN'QTY+153:2'LIN+86++999999995388 8:EN'QTY+153:-3'LIN+87++99 99999954267:EN'QTY+153:3'LIN+88++9999999954328:EN' QTY+153:1'LIN+89++99999999 56384:EN'QTY+153:5'LIN+90++9999999956391:EN'QTY+15 3:9'LIN+91++9999999956407: EN'QTY+153:3'LIN+92++9999999957015:EN'QTY+153:8'LI N+93++9999999957022:EN'QTY +153:4'LIN+94++9999999957251:EN'QTY+153:-2'LIN+95++9999999957473:EN'QTY+153: 1'LIN+96++9999999957619:EN'QTY+153:1'LIN+97++99999 99957657:EN'QTY+153:-2'LIN +98++9999999957695:EN'QTY+153:1'LIN+99++9999999957 732:EN'QTY+153:1'LIN+100++ 9999999957749:EN'QTY+153:6'LIN+101++9999999957886: EN'QTY+153:2'LIN+102++9999 999957985:EN'QTY+153:5'LIN+103++9999999958005:EN'Q TY+153:1'LIN+104++99999999 58821:EN'QTY+153:8'LIN+105++9999999960657:EN'QTY+1 53:-1'LIN+106++99999999616 16:EN'QTY+153:2'LIN+107++9999999970199:EN'QTY+153: 6'LIN+108++9999999970205:E N'QTY+153:5'LIN+109++9999999970557:EN'QTY+153:2'LO C+162+5023949223920::9'LIN +110++5014838064023:EN'QTY+153:3'LIN+111++99999999 29029:EN'QTY+153:1'LIN+112 ++9999999929951:EN'QTY+153:2'LIN+113++999999992996 8:EN'QTY+153:-1'LIN+114++9 999999938557:EN'QTY+153:1'LIN+115++9999999949560:E N'QTY+153:2'LIN+116++99999 99953154:EN'QTY+153:4'LIN+117++9999999953253:EN'QT Y+153:3'LIN+118++999999995 3284:EN'QTY+153:1'LIN+119++9999999953352:EN'QTY+15 3:1'LIN+120++9999999953390 :EN'QTY+153:1'LIN+121++9999999953888:EN'QTY+153:3' LIN+122++9999999956384:EN' QTY+153:5'LIN+123++9999999956391:EN'QTY+153:14'LIN +124++9999999956407:EN'QTY +153:2'LIN+125++9999999957015:EN'QTY+153:3'LIN+126 ++9999999957183:EN'QTY+153 :3'LIN+127++9999999957251:EN'QTY+153:2'LIN+128++99 99999957671:EN'QTY+153:4'L IN+129++9999999957688:EN'QTY+153:2'LIN+130++999999 9957701:EN'QTY+153:-1'LIN+ 131++9999999957794:EN'QTY+153:2'LIN+132++999999995 7985:EN'QTY+153:1'LIN+133+ +9999999961616:EN'QTY+153:7'LIN+134++9999999970199 :EN'QTY+153:1'LOC+162+5023 949232199::9'LIN+135++9999999923492:EN'QTY+153:1'L IN+136++9999999951419:EN'Q TY+153:2'LIN+137++9999999953147:EN'QTY+153:-1'LIN+138++9999999953253:EN'QTY+ 153:8'LIN+139++9999999953260:EN'QTY+153:3'LIN+140+ +9999999953277:EN'QTY+153: 2'LIN+141++9999999953345:EN'QTY+153:1'LIN+142++999 9999953888:EN'QTY+153:2'LI N+143++9999999956384:EN'QTY+153:3'LIN+144++9999999 956391:EN'QTY+153:1'LIN+14 5++9999999956407:EN'QTY+153:1'LIN+146++99999999570 15:EN'QTY+153:1'LIN+147++9 999999957022:EN'QTY+153:4'LIN+148++9999999957749:E N'QTY+153:3'LIN+149++99999 99957909:EN'QTY+153:2'LIN+150++9999999957985:EN'QT Y+153:2'LIN+151++999999997 0540:EN'QTY+153:2'UNT+1608+22'UNZ+1+435' |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Importing Long String - String Manipulation (EDI EANCOM 96a)
Thanks Tom, thats fantastic!
The first line I would basically delete (the information will not be used at this time).. Basically the LOC is a separator for the StoreID followed by all the LIN orders until the next LOC. I would like to get it so i get the (STOREID) Column1 9999999988888 (LINE) (PRODUCTID) (QTY) Column2 Column3 Column4 1 9999999912345 1 2 9999999923456 2 3 9999999934567 1 4 9999999954321 3 column 1 would be the line, column 2 the ProductID and column 3 the Qty It would be good if i could separate the lines by LOC so i get Brian "Tom Ogilvy" wrote in message ... Here is a start. You would have to tell me what to do with the first line and what the LOC lines are. Sub testme2() Dim FName As String Dim FNum As Long Dim l As String Dim l1 As Variant FName = "C:\SLSRPT.txt" FNum = FreeFile Open FName For Input As FNum Line Input #FNum, l l1 = Split(l, "LIN+") Cells(1, 1).Resize(UBound(l1) - _ LBound(l1) + 1).Value = Application. _ Transpose(l1) Close #FNum End Sub -- regards, Tom Ogilvy "Brian" wrote in message ... I have a long text file , some of you may recognise it as a EDI EANCOM 96a file... Basically, this file is sent via EDI, but is not imported into our systems. It is purely used to get sales figures 'LIN+1++9999999932234:EN'QTY+153:1 each line is prefixed with 'LIN+ 'LOC+162+9999999949625::9 will give me the location However the file is one long string, not CSV etc so i have to find a way to get the imported file to split into rows at each 'LIN+ i have managed to get the text to import, but all in one cell, or split into the world allocation of cells... I would like to be able to get the file to dump into a sheet as follows LOC 9999999949625 LIN STORE QTY 153 QTY 200 QTY 301 1 999999932234 1 2 9999999953277 1 3 9999999956407 1 4 9999999970212 2 Note i also recieve an Inventory File that has a similar format (QTY 200, 301 etc ), so i want to import that at some point to... Once i get the idea on the best wat to split the file with one QTY i will look at the bigger one... As you can see from the text extract below of the file , its not fixed width of very easy to split.... THIS DONT WORK... ________________________________________ Sub testme2() Dim FName As String Dim KeepTheNext As Boolean Dim FNum As Long Dim i As Long Dim sLine As String FName = "C:\SLSRPT.txt" FNum = FreeFile Open FName For Input As FNum i = 1 KeepTheNext = False Do While Not EOF(FNum) Input #FNum, sLine If InStr(1, sLine, "'LIN+", vbTextCompare) 0 Then KeepTheNext = True Else If KeepTheNext = True Then KeepTheNext = False Cells(i, 1).Value = sLine ' Exit Do i = i + 1 End If End If Loop End Sub HELP!!!!! Thanks for any advice in advance Brian ________________________________________ SAMPLE OF TEXT.... UNB+UNOA:3+999999990004:14+9999999900001+060205:05 08+435+ +SLSRPT'UNH+22+SLSRPT:D:96A:UN:EAN008'BGM+73E::9+0 0000014+9'DTM+356:20060129 -20060204:718'NAD+FR+9999999900004::9'NAD+SU+999999 9900001::9'LOC+162+999999 9949625::9'LIN+1++9999999932234:EN'QTY+153:1'LIN+2 ++9999999953277:EN'QTY+153 :-1'LIN+3++9999999956407:EN'QTY+153:1'LIN+4++9999999 970212:EN'QTY+153:2'LOC+ 162+5023949057895::9'LIN+5++21481505:EN'QTY+153:1' LIN+6++9999999923492:EN'QT Y+153:-1'LIN+7++9999999929029:EN'QTY+153:2'LIN+8++9999999 932418:EN'QTY+153:1 'LIN+9++9999999937482:EN'QTY+153:1'LIN+10++9999999 949560:EN'QTY+153:-1'LIN+1 1++9999999953253:EN'QTY+153:2'LIN+12++999999995388 8:EN'QTY+153:3'LIN+13++999 9999954205:EN'QTY+153:2'LIN+14++9999999954250:EN'Q TY+153:1'LIN+15++999999995 6407:EN'QTY+153:1'LIN+16++9999999957015:EN'QTY+153 :7'LIN+17++9999999957022:E N'QTY+153:2'LIN+18++9999999957473:EN'QTY+153:1'LIN +19++9999999957688:EN'QTY+ 153:3'LIN+20++9999999957794:EN'QTY+153:2'LIN+21++9 999999970496:EN'QTY+153:3' LOC+162+5023949136774::9'LIN+22++21326806:EN'QTY+1 53:1'LIN+23++9999999923492 :EN'QTY+153:2'LIN+24++9999999929029:EN'QTY+153:1'L IN+25++9999999932418:EN'QT Y+153:2'LIN+26++9999999953253:EN'QTY+153:1'LIN+27+ +9999999953260:EN'QTY+153: 1'LIN+28++9999999953284:EN'QTY+153:1'LIN+29++99999 99953307:EN'QTY+153:1'LIN+ 30++9999999953338:EN'QTY+153:3'LIN+31++99999999533 45:EN'QTY+153:3'LIN+32++99 99999953383:EN'QTY+153:-1'LIN+33++9999999953888:EN'QTY+153:7'LIN+34++99999 99 956391:EN'QTY+153:1'LIN+35++9999999956407:EN'QTY+1 53:1'LIN+36++9999999957015 :EN'QTY+153:2'LIN+37++9999999957022:EN'QTY+153:1'L IN+38++9999999957183:EN'QT Y+153:1'LIN+39++9999999957688:EN'QTY+153:2'LIN+40+ +9999999957695:EN'QTY+153: 1'LIN+41++9999999957749:EN'QTY+153:7'LIN+42++99999 99957794:EN'QTY+153:10'LIN +43++9999999957879:EN'QTY+153:1'LIN+44++9999999957 985:EN'QTY+153:1'LIN+45++9 999999958821:EN'QTY+153:-1'LIN+46++9999999961616:EN'QTY+153:3'LIN+47++99999 9 9970199:EN'QTY+153:4'LIN+48++9999999970205:EN'QTY+ 153:2'LIN+49++999999997021 2:EN'QTY+153:5'LIN+50++9999999970649:EN'QTY+153:2' LOC+162+5023949145040::9'L IN+51++5014838064023:EN'QTY+153:3'LIN+52++99999999 23492:EN'QTY+153:1'LIN+53+ +9999999929029:EN'QTY+153:1'LIN+54++9999999932234: EN'QTY+153:3'LIN+55++99999 99938557:EN'QTY+153:1'LIN+56++9999999953253:EN'QTY +153:2'LIN+57++99999999532 77:EN'QTY+153:2'LIN+58++9999999953284:EN'QTY+153:1 'LIN+59++9999999953888:EN' QTY+153:3'LIN+60++9999999954281:EN'QTY+153:1'LIN+6 1++9999999957015:EN'QTY+15 3:6'LIN+62++9999999957022:EN'QTY+153:5'LIN+63++999 9999957671:EN'QTY+153:2'LI N+64++9999999957794:EN'QTY+153:-1'LIN+65++9999999958821:EN'QTY+153:2'LIN+66+ +9999999970205:EN'QTY+153:1'LOC+162+5023949182579: :9'LIN+67++21326806:EN'QTY +153:1'LIN+68++9999999923492:EN'QTY+153:3'LIN+69++ 9999999929029:EN'QTY+153:1 'LIN+70++9999999929951:EN'QTY+153:-1'LIN+71++9999999932234:EN'QTY+153:2'LIN+ 72++9999999937482:EN'QTY+153:2'LIN+73++99999999385 57:EN'QTY+153:3'LIN+74++99 99999943353:EN'QTY+153:1'LIN+75++9999999951419:EN' QTY+153:1'LIN+76++99999999 52942:EN'QTY+153:2'LIN+77++9999999953079:EN'QTY+15 3:6'LIN+78++9999999953147: EN'QTY+153:1'LIN+79++9999999953154:EN'QTY+153:1'LI N+80++9999999953253:EN'QTY +153:1'LIN+81++9999999953260:EN'QTY+153:4'LIN+82++ 9999999953277:EN'QTY+153:1 'LIN+83++9999999953307:EN'QTY+153:1'LIN+84++999999 9953338:EN'QTY+153:2'LIN+8 5++9999999953352:EN'QTY+153:2'LIN+86++999999995388 8:EN'QTY+153:-3'LIN+87++99 99999954267:EN'QTY+153:3'LIN+88++9999999954328:EN' QTY+153:1'LIN+89++99999999 56384:EN'QTY+153:5'LIN+90++9999999956391:EN'QTY+15 3:9'LIN+91++9999999956407: EN'QTY+153:3'LIN+92++9999999957015:EN'QTY+153:8'LI N+93++9999999957022:EN'QTY +153:4'LIN+94++9999999957251:EN'QTY+153:-2'LIN+95++9999999957473:EN'QTY+153: 1'LIN+96++9999999957619:EN'QTY+153:1'LIN+97++99999 99957657:EN'QTY+153:-2'LIN +98++9999999957695:EN'QTY+153:1'LIN+99++9999999957 732:EN'QTY+153:1'LIN+100++ 9999999957749:EN'QTY+153:6'LIN+101++9999999957886: EN'QTY+153:2'LIN+102++9999 999957985:EN'QTY+153:5'LIN+103++9999999958005:EN'Q TY+153:1'LIN+104++99999999 58821:EN'QTY+153:8'LIN+105++9999999960657:EN'QTY+1 53:-1'LIN+106++99999999616 16:EN'QTY+153:2'LIN+107++9999999970199:EN'QTY+153: 6'LIN+108++9999999970205:E N'QTY+153:5'LIN+109++9999999970557:EN'QTY+153:2'LO C+162+5023949223920::9'LIN +110++5014838064023:EN'QTY+153:3'LIN+111++99999999 29029:EN'QTY+153:1'LIN+112 ++9999999929951:EN'QTY+153:2'LIN+113++999999992996 8:EN'QTY+153:-1'LIN+114++9 999999938557:EN'QTY+153:1'LIN+115++9999999949560:E N'QTY+153:2'LIN+116++99999 99953154:EN'QTY+153:4'LIN+117++9999999953253:EN'QT Y+153:3'LIN+118++999999995 3284:EN'QTY+153:1'LIN+119++9999999953352:EN'QTY+15 3:1'LIN+120++9999999953390 :EN'QTY+153:1'LIN+121++9999999953888:EN'QTY+153:3' LIN+122++9999999956384:EN' QTY+153:5'LIN+123++9999999956391:EN'QTY+153:14'LIN +124++9999999956407:EN'QTY +153:2'LIN+125++9999999957015:EN'QTY+153:3'LIN+126 ++9999999957183:EN'QTY+153 :3'LIN+127++9999999957251:EN'QTY+153:2'LIN+128++99 99999957671:EN'QTY+153:4'L IN+129++9999999957688:EN'QTY+153:2'LIN+130++999999 9957701:EN'QTY+153:-1'LIN+ 131++9999999957794:EN'QTY+153:2'LIN+132++999999995 7985:EN'QTY+153:1'LIN+133+ +9999999961616:EN'QTY+153:7'LIN+134++9999999970199 :EN'QTY+153:1'LOC+162+5023 949232199::9'LIN+135++9999999923492:EN'QTY+153:1'L IN+136++9999999951419:EN'Q TY+153:2'LIN+137++9999999953147:EN'QTY+153:-1'LIN+138++9999999953253:EN'QTY+ 153:8'LIN+139++9999999953260:EN'QTY+153:3'LIN+140+ +9999999953277:EN'QTY+153: 2'LIN+141++9999999953345:EN'QTY+153:1'LIN+142++999 9999953888:EN'QTY+153:2'LI N+143++9999999956384:EN'QTY+153:3'LIN+144++9999999 956391:EN'QTY+153:1'LIN+14 5++9999999956407:EN'QTY+153:1'LIN+146++99999999570 15:EN'QTY+153:1'LIN+147++9 999999957022:EN'QTY+153:4'LIN+148++9999999957749:E N'QTY+153:3'LIN+149++99999 99957909:EN'QTY+153:2'LIN+150++9999999957985:EN'QT Y+153:2'LIN+151++999999997 0540:EN'QTY+153:2'UNT+1608+22'UNZ+1+435' |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Importing Long String - String Manipulation (EDI EANCOM 96a)
Sub testme2()
Dim FName As String Dim FNum As Long Dim l As String Dim l1 As Variant Dim s As String Dim sChr As String Dim rng1 As Range, rng As Range Dim cell As Range, iloc As Long Columns("A:D").ClearContents Columns(3).NumberFormat = _ "0000000000000" FName = "C:\SLSRPT.txt" FNum = FreeFile Open FName For Input As FNum Line Input #FNum, s s = Application.Clean(s) s = Replace(s, Chr(9), "") l = s l = Replace(l, "LIN+", "LIN+,") l = Replace(l, "LOC", "LIN+LOC") l = Replace(l, ":EN'QTY+153:", ",") l = Replace(l, "'", "") ' l = Replace(l, "+", ",") l1 = Split(l, "LIN+") Cells(1, 1).Resize(UBound(l1) - _ LBound(l1) + 1).Value = Application. _ Transpose(l1) Close #FNum Rows(1).Delete Columns(1).Replace "++", "," Columns(1).TextToColumns _ Destination:=Range("A1"), _ DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, _ ConsecutiveDelimiter:=False, _ Tab:=False, _ Semicolon:=False, _ Comma:=True, _ Space:=False, _ Other:=False, _ FieldInfo:=Array( _ Array(1, 1), _ Array(2, 1), _ Array(3, 1), _ Array(4, 1)) Set rng1 = Cells(Rows.Count, 4).End(xlUp) iloc = InStr(1, rng1, "UN", vbTextCompare) rng1 = Left(rng1, iloc - 1) Set rng = Columns(1).SpecialCells(xlConstants) For Each cell In rng iloc = InStr(1, cell, "+", vbTextCompare) iloc = InStr(iloc + 1, cell, "+", vbTextCompare) cell.Value = "'" & Mid(cell, iloc + 1, 13) Next End Sub -- Regards, Tom Ogilvy "Brian" wrote in message ... Thanks Tom, thats fantastic! The first line I would basically delete (the information will not be used at this time).. Basically the LOC is a separator for the StoreID followed by all the LIN orders until the next LOC. I would like to get it so i get the (STOREID) Column1 9999999988888 (LINE) (PRODUCTID) (QTY) Column2 Column3 Column4 1 9999999912345 1 2 9999999923456 2 3 9999999934567 1 4 9999999954321 3 column 1 would be the line, column 2 the ProductID and column 3 the Qty It would be good if i could separate the lines by LOC so i get Brian "Tom Ogilvy" wrote in message ... Here is a start. You would have to tell me what to do with the first line and what the LOC lines are. Sub testme2() Dim FName As String Dim FNum As Long Dim l As String Dim l1 As Variant FName = "C:\SLSRPT.txt" FNum = FreeFile Open FName For Input As FNum Line Input #FNum, l l1 = Split(l, "LIN+") Cells(1, 1).Resize(UBound(l1) - _ LBound(l1) + 1).Value = Application. _ Transpose(l1) Close #FNum End Sub -- regards, Tom Ogilvy "Brian" wrote in message ... I have a long text file , some of you may recognise it as a EDI EANCOM 96a file... Basically, this file is sent via EDI, but is not imported into our systems. It is purely used to get sales figures 'LIN+1++9999999932234:EN'QTY+153:1 each line is prefixed with 'LIN+ 'LOC+162+9999999949625::9 will give me the location However the file is one long string, not CSV etc so i have to find a way to get the imported file to split into rows at each 'LIN+ i have managed to get the text to import, but all in one cell, or split into the world allocation of cells... I would like to be able to get the file to dump into a sheet as follows LOC 9999999949625 LIN STORE QTY 153 QTY 200 QTY 301 1 999999932234 1 2 9999999953277 1 3 9999999956407 1 4 9999999970212 2 Note i also recieve an Inventory File that has a similar format (QTY 200, 301 etc ), so i want to import that at some point to... Once i get the idea on the best wat to split the file with one QTY i will look at the bigger one... As you can see from the text extract below of the file , its not fixed width of very easy to split.... THIS DONT WORK... ________________________________________ Sub testme2() Dim FName As String Dim KeepTheNext As Boolean Dim FNum As Long Dim i As Long Dim sLine As String FName = "C:\SLSRPT.txt" FNum = FreeFile Open FName For Input As FNum i = 1 KeepTheNext = False Do While Not EOF(FNum) Input #FNum, sLine If InStr(1, sLine, "'LIN+", vbTextCompare) 0 Then KeepTheNext = True Else If KeepTheNext = True Then KeepTheNext = False Cells(i, 1).Value = sLine ' Exit Do i = i + 1 End If End If Loop End Sub HELP!!!!! Thanks for any advice in advance Brian ________________________________________ SAMPLE OF TEXT.... UNB+UNOA:3+999999990004:14+9999999900001+060205:05 08+435+ +SLSRPT'UNH+22+SLSRPT:D:96A:UN:EAN008'BGM+73E::9+0 0000014+9'DTM+356:20060129 -20060204:718'NAD+FR+9999999900004::9'NAD+SU+999999 9900001::9'LOC+162+9999 99 9949625::9'LIN+1++9999999932234:EN'QTY+153:1'LIN+2 ++9999999953277:EN'QTY+153 :-1'LIN+3++9999999956407:EN'QTY+153:1'LIN+4++9999999 970212:EN'QTY+153:2'LOC+ 162+5023949057895::9'LIN+5++21481505:EN'QTY+153:1' LIN+6++9999999923492:EN'QT Y+153:-1'LIN+7++9999999929029:EN'QTY+153:2'LIN+8++9999999 932418:EN'QTY+153:1 'LIN+9++9999999937482:EN'QTY+153:1'LIN+10++9999999 949560:EN'QTY+153:-1'LIN+1 1++9999999953253:EN'QTY+153:2'LIN+12++999999995388 8:EN'QTY+153:3'LIN+13++999 9999954205:EN'QTY+153:2'LIN+14++9999999954250:EN'Q TY+153:1'LIN+15++999999995 6407:EN'QTY+153:1'LIN+16++9999999957015:EN'QTY+153 :7'LIN+17++9999999957022:E N'QTY+153:2'LIN+18++9999999957473:EN'QTY+153:1'LIN +19++9999999957688:EN'QTY+ 153:3'LIN+20++9999999957794:EN'QTY+153:2'LIN+21++9 999999970496:EN'QTY+153:3' LOC+162+5023949136774::9'LIN+22++21326806:EN'QTY+1 53:1'LIN+23++9999999923492 :EN'QTY+153:2'LIN+24++9999999929029:EN'QTY+153:1'L IN+25++9999999932418:EN'QT Y+153:2'LIN+26++9999999953253:EN'QTY+153:1'LIN+27+ +9999999953260:EN'QTY+153: 1'LIN+28++9999999953284:EN'QTY+153:1'LIN+29++99999 99953307:EN'QTY+153:1'LIN+ 30++9999999953338:EN'QTY+153:3'LIN+31++99999999533 45:EN'QTY+153:3'LIN+32++99 99999953383:EN'QTY+153:-1'LIN+33++9999999953888:EN'QTY+153:7'LIN+34++99999 99 956391:EN'QTY+153:1'LIN+35++9999999956407:EN'QTY+1 53:1'LIN+36++9999999957015 :EN'QTY+153:2'LIN+37++9999999957022:EN'QTY+153:1'L IN+38++9999999957183:EN'QT Y+153:1'LIN+39++9999999957688:EN'QTY+153:2'LIN+40+ +9999999957695:EN'QTY+153: 1'LIN+41++9999999957749:EN'QTY+153:7'LIN+42++99999 99957794:EN'QTY+153:10'LIN +43++9999999957879:EN'QTY+153:1'LIN+44++9999999957 985:EN'QTY+153:1'LIN+45++9 999999958821:EN'QTY+153:-1'LIN+46++9999999961616:EN'QTY+153:3'LIN+47++99999 9 9970199:EN'QTY+153:4'LIN+48++9999999970205:EN'QTY+ 153:2'LIN+49++999999997021 2:EN'QTY+153:5'LIN+50++9999999970649:EN'QTY+153:2' LOC+162+5023949145040::9'L IN+51++5014838064023:EN'QTY+153:3'LIN+52++99999999 23492:EN'QTY+153:1'LIN+53+ +9999999929029:EN'QTY+153:1'LIN+54++9999999932234: EN'QTY+153:3'LIN+55++99999 99938557:EN'QTY+153:1'LIN+56++9999999953253:EN'QTY +153:2'LIN+57++99999999532 77:EN'QTY+153:2'LIN+58++9999999953284:EN'QTY+153:1 'LIN+59++9999999953888:EN' QTY+153:3'LIN+60++9999999954281:EN'QTY+153:1'LIN+6 1++9999999957015:EN'QTY+15 3:6'LIN+62++9999999957022:EN'QTY+153:5'LIN+63++999 9999957671:EN'QTY+153:2'LI N+64++9999999957794:EN'QTY+153:-1'LIN+65++9999999958821:EN'QTY+153:2'LIN+66+ +9999999970205:EN'QTY+153:1'LOC+162+5023949182579: :9'LIN+67++21326806:EN'QTY +153:1'LIN+68++9999999923492:EN'QTY+153:3'LIN+69++ 9999999929029:EN'QTY+153:1 'LIN+70++9999999929951:EN'QTY+153:-1'LIN+71++9999999932234:EN'QTY+153:2'LIN+ 72++9999999937482:EN'QTY+153:2'LIN+73++99999999385 57:EN'QTY+153:3'LIN+74++99 99999943353:EN'QTY+153:1'LIN+75++9999999951419:EN' QTY+153:1'LIN+76++99999999 52942:EN'QTY+153:2'LIN+77++9999999953079:EN'QTY+15 3:6'LIN+78++9999999953147: EN'QTY+153:1'LIN+79++9999999953154:EN'QTY+153:1'LI N+80++9999999953253:EN'QTY +153:1'LIN+81++9999999953260:EN'QTY+153:4'LIN+82++ 9999999953277:EN'QTY+153:1 'LIN+83++9999999953307:EN'QTY+153:1'LIN+84++999999 9953338:EN'QTY+153:2'LIN+8 5++9999999953352:EN'QTY+153:2'LIN+86++999999995388 8:EN'QTY+153:-3'LIN+87++99 99999954267:EN'QTY+153:3'LIN+88++9999999954328:EN' QTY+153:1'LIN+89++99999999 56384:EN'QTY+153:5'LIN+90++9999999956391:EN'QTY+15 3:9'LIN+91++9999999956407: EN'QTY+153:3'LIN+92++9999999957015:EN'QTY+153:8'LI N+93++9999999957022:EN'QTY +153:4'LIN+94++9999999957251:EN'QTY+153:-2'LIN+95++9999999957473:EN'QTY+153: 1'LIN+96++9999999957619:EN'QTY+153:1'LIN+97++99999 99957657:EN'QTY+153:-2'LIN +98++9999999957695:EN'QTY+153:1'LIN+99++9999999957 732:EN'QTY+153:1'LIN+100++ 9999999957749:EN'QTY+153:6'LIN+101++9999999957886: EN'QTY+153:2'LIN+102++9999 999957985:EN'QTY+153:5'LIN+103++9999999958005:EN'Q TY+153:1'LIN+104++99999999 58821:EN'QTY+153:8'LIN+105++9999999960657:EN'QTY+1 53:-1'LIN+106++99999999616 16:EN'QTY+153:2'LIN+107++9999999970199:EN'QTY+153: 6'LIN+108++9999999970205:E N'QTY+153:5'LIN+109++9999999970557:EN'QTY+153:2'LO C+162+5023949223920::9'LIN +110++5014838064023:EN'QTY+153:3'LIN+111++99999999 29029:EN'QTY+153:1'LIN+112 ++9999999929951:EN'QTY+153:2'LIN+113++999999992996 8:EN'QTY+153:-1'LIN+114++9 999999938557:EN'QTY+153:1'LIN+115++9999999949560:E N'QTY+153:2'LIN+116++99999 99953154:EN'QTY+153:4'LIN+117++9999999953253:EN'QT Y+153:3'LIN+118++999999995 3284:EN'QTY+153:1'LIN+119++9999999953352:EN'QTY+15 3:1'LIN+120++9999999953390 :EN'QTY+153:1'LIN+121++9999999953888:EN'QTY+153:3' LIN+122++9999999956384:EN' QTY+153:5'LIN+123++9999999956391:EN'QTY+153:14'LIN +124++9999999956407:EN'QTY +153:2'LIN+125++9999999957015:EN'QTY+153:3'LIN+126 ++9999999957183:EN'QTY+153 :3'LIN+127++9999999957251:EN'QTY+153:2'LIN+128++99 99999957671:EN'QTY+153:4'L IN+129++9999999957688:EN'QTY+153:2'LIN+130++999999 9957701:EN'QTY+153:-1'LIN+ 131++9999999957794:EN'QTY+153:2'LIN+132++999999995 7985:EN'QTY+153:1'LIN+133+ +9999999961616:EN'QTY+153:7'LIN+134++9999999970199 :EN'QTY+153:1'LOC+162+5023 949232199::9'LIN+135++9999999923492:EN'QTY+153:1'L IN+136++9999999951419:EN'Q TY+153:2'LIN+137++9999999953147:EN'QTY+153:-1'LIN+138++9999999953253:EN'QTY+ 153:8'LIN+139++9999999953260:EN'QTY+153:3'LIN+140+ +9999999953277:EN'QTY+153: 2'LIN+141++9999999953345:EN'QTY+153:1'LIN+142++999 9999953888:EN'QTY+153:2'LI N+143++9999999956384:EN'QTY+153:3'LIN+144++9999999 956391:EN'QTY+153:1'LIN+14 5++9999999956407:EN'QTY+153:1'LIN+146++99999999570 15:EN'QTY+153:1'LIN+147++9 999999957022:EN'QTY+153:4'LIN+148++9999999957749:E N'QTY+153:3'LIN+149++99999 99957909:EN'QTY+153:2'LIN+150++9999999957985:EN'QT Y+153:2'LIN+151++999999997 0540:EN'QTY+153:2'UNT+1608+22'UNZ+1+435' |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Importing Long String - String Manipulation (EDI EANCOM 96a)
That worked perfectly!,. however, what i thought i wanted wasnt quite
right... If I wanted to get into into a format like: STOREID EAN QTY153 5023949049625 5014838132234 1 5023949049625 5014838153277 -1 5023949049625 5014838156407 1 5023949049625 5014838170212 2 So i only ended up with 3 columns (ditching the LIN no). I would also want to automatically add the headings at A1:C1 For a pretty readabl;e report I decided to pivot the data, which works very nicely for the user, only I had to copy the STOREID along side every EAN entry. I was hoping that looking through an example formula would make it much clearer exactly how it works.... I have been fiddling, but not been sucessful as yet. Any chance you could add some comments to your snippet which might help me... Thanks for all your help, i have actually got the string into a readable pivot, just like to automate that last bit, before i look at the next part of these reports! Brian "Tom Ogilvy" wrote in message ... Sub testme2() Dim FName As String Dim FNum As Long Dim l As String Dim l1 As Variant Dim s As String Dim sChr As String Dim rng1 As Range, rng As Range Dim cell As Range, iloc As Long Columns("A:D").ClearContents Columns(3).NumberFormat = _ "0000000000000" FName = "C:\SLSRPT.txt" FNum = FreeFile Open FName For Input As FNum Line Input #FNum, s s = Application.Clean(s) s = Replace(s, Chr(9), "") l = s l = Replace(l, "LIN+", "LIN+,") l = Replace(l, "LOC", "LIN+LOC") l = Replace(l, ":EN'QTY+153:", ",") l = Replace(l, "'", "") ' l = Replace(l, "+", ",") l1 = Split(l, "LIN+") Cells(1, 1).Resize(UBound(l1) - _ LBound(l1) + 1).Value = Application. _ Transpose(l1) Close #FNum Rows(1).Delete Columns(1).Replace "++", "," Columns(1).TextToColumns _ Destination:=Range("A1"), _ DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, _ ConsecutiveDelimiter:=False, _ Tab:=False, _ Semicolon:=False, _ Comma:=True, _ Space:=False, _ Other:=False, _ FieldInfo:=Array( _ Array(1, 1), _ Array(2, 1), _ Array(3, 1), _ Array(4, 1)) Set rng1 = Cells(Rows.Count, 4).End(xlUp) iloc = InStr(1, rng1, "UN", vbTextCompare) rng1 = Left(rng1, iloc - 1) Set rng = Columns(1).SpecialCells(xlConstants) For Each cell In rng iloc = InStr(1, cell, "+", vbTextCompare) iloc = InStr(iloc + 1, cell, "+", vbTextCompare) cell.Value = "'" & Mid(cell, iloc + 1, 13) Next End Sub -- Regards, Tom Ogilvy "Brian" wrote in message ... Thanks Tom, thats fantastic! The first line I would basically delete (the information will not be used at this time).. Basically the LOC is a separator for the StoreID followed by all the LIN orders until the next LOC. I would like to get it so i get the (STOREID) Column1 9999999988888 (LINE) (PRODUCTID) (QTY) Column2 Column3 Column4 1 9999999912345 1 2 9999999923456 2 3 9999999934567 1 4 9999999954321 3 column 1 would be the line, column 2 the ProductID and column 3 the Qty It would be good if i could separate the lines by LOC so i get Brian "Tom Ogilvy" wrote in message ... Here is a start. You would have to tell me what to do with the first line and what the LOC lines are. Sub testme2() Dim FName As String Dim FNum As Long Dim l As String Dim l1 As Variant FName = "C:\SLSRPT.txt" FNum = FreeFile Open FName For Input As FNum Line Input #FNum, l l1 = Split(l, "LIN+") Cells(1, 1).Resize(UBound(l1) - _ LBound(l1) + 1).Value = Application. _ Transpose(l1) Close #FNum End Sub -- regards, Tom Ogilvy "Brian" wrote in message ... I have a long text file , some of you may recognise it as a EDI EANCOM 96a file... Basically, this file is sent via EDI, but is not imported into our systems. It is purely used to get sales figures 'LIN+1++9999999932234:EN'QTY+153:1 each line is prefixed with 'LIN+ 'LOC+162+9999999949625::9 will give me the location However the file is one long string, not CSV etc so i have to find a way to get the imported file to split into rows at each 'LIN+ i have managed to get the text to import, but all in one cell, or split into the world allocation of cells... I would like to be able to get the file to dump into a sheet as follows LOC 9999999949625 LIN STORE QTY 153 QTY 200 QTY 301 1 999999932234 1 2 9999999953277 1 3 9999999956407 1 4 9999999970212 2 Note i also recieve an Inventory File that has a similar format (QTY 200, 301 etc ), so i want to import that at some point to... Once i get the idea on the best wat to split the file with one QTY i will look at the bigger one... As you can see from the text extract below of the file , its not fixed width of very easy to split.... THIS DONT WORK... ________________________________________ Sub testme2() Dim FName As String Dim KeepTheNext As Boolean Dim FNum As Long Dim i As Long Dim sLine As String FName = "C:\SLSRPT.txt" FNum = FreeFile Open FName For Input As FNum i = 1 KeepTheNext = False Do While Not EOF(FNum) Input #FNum, sLine If InStr(1, sLine, "'LIN+", vbTextCompare) 0 Then KeepTheNext = True Else If KeepTheNext = True Then KeepTheNext = False Cells(i, 1).Value = sLine ' Exit Do i = i + 1 End If End If Loop End Sub HELP!!!!! Thanks for any advice in advance Brian ________________________________________ SAMPLE OF TEXT.... UNB+UNOA:3+999999990004:14+9999999900001+060205:05 08+435+ +SLSRPT'UNH+22+SLSRPT:D:96A:UN:EAN008'BGM+73E::9+0 0000014+9'DTM+356:20060129 -20060204:718'NAD+FR+9999999900004::9'NAD+SU+999999 9900001::9'LOC+162+9999 99 9949625::9'LIN+1++9999999932234:EN'QTY+153:1'LIN+2 ++9999999953277:EN'QTY+153 :-1'LIN+3++9999999956407:EN'QTY+153:1'LIN+4++9999999 970212:EN'QTY+153:2'LOC+ 162+5023949057895::9'LIN+5++21481505:EN'QTY+153:1' LIN+6++9999999923492:EN'QT Y+153:-1'LIN+7++9999999929029:EN'QTY+153:2'LIN+8++9999999 932418:EN'QTY+153:1 'LIN+9++9999999937482:EN'QTY+153:1'LIN+10++9999999 949560:EN'QTY+153:-1'LIN+1 1++9999999953253:EN'QTY+153:2'LIN+12++999999995388 8:EN'QTY+153:3'LIN+13++999 9999954205:EN'QTY+153:2'LIN+14++9999999954250:EN'Q TY+153:1'LIN+15++999999995 6407:EN'QTY+153:1'LIN+16++9999999957015:EN'QTY+153 :7'LIN+17++9999999957022:E N'QTY+153:2'LIN+18++9999999957473:EN'QTY+153:1'LIN +19++9999999957688:EN'QTY+ 153:3'LIN+20++9999999957794:EN'QTY+153:2'LIN+21++9 999999970496:EN'QTY+153:3' LOC+162+5023949136774::9'LIN+22++21326806:EN'QTY+1 53:1'LIN+23++9999999923492 :EN'QTY+153:2'LIN+24++9999999929029:EN'QTY+153:1'L IN+25++9999999932418:EN'QT Y+153:2'LIN+26++9999999953253:EN'QTY+153:1'LIN+27+ +9999999953260:EN'QTY+153: 1'LIN+28++9999999953284:EN'QTY+153:1'LIN+29++99999 99953307:EN'QTY+153:1'LIN+ 30++9999999953338:EN'QTY+153:3'LIN+31++99999999533 45:EN'QTY+153:3'LIN+32++99 99999953383:EN'QTY+153:-1'LIN+33++9999999953888:EN'QTY+153:7'LIN+34++99999 99 956391:EN'QTY+153:1'LIN+35++9999999956407:EN'QTY+1 53:1'LIN+36++9999999957015 :EN'QTY+153:2'LIN+37++9999999957022:EN'QTY+153:1'L IN+38++9999999957183:EN'QT Y+153:1'LIN+39++9999999957688:EN'QTY+153:2'LIN+40+ +9999999957695:EN'QTY+153: 1'LIN+41++9999999957749:EN'QTY+153:7'LIN+42++99999 99957794:EN'QTY+153:10'LIN +43++9999999957879:EN'QTY+153:1'LIN+44++9999999957 985:EN'QTY+153:1'LIN+45++9 999999958821:EN'QTY+153:-1'LIN+46++9999999961616:EN'QTY+153:3'LIN+47++99999 9 9970199:EN'QTY+153:4'LIN+48++9999999970205:EN'QTY+ 153:2'LIN+49++999999997021 2:EN'QTY+153:5'LIN+50++9999999970649:EN'QTY+153:2' LOC+162+5023949145040::9'L IN+51++5014838064023:EN'QTY+153:3'LIN+52++99999999 23492:EN'QTY+153:1'LIN+53+ +9999999929029:EN'QTY+153:1'LIN+54++9999999932234: EN'QTY+153:3'LIN+55++99999 99938557:EN'QTY+153:1'LIN+56++9999999953253:EN'QTY +153:2'LIN+57++99999999532 77:EN'QTY+153:2'LIN+58++9999999953284:EN'QTY+153:1 'LIN+59++9999999953888:EN' QTY+153:3'LIN+60++9999999954281:EN'QTY+153:1'LIN+6 1++9999999957015:EN'QTY+15 3:6'LIN+62++9999999957022:EN'QTY+153:5'LIN+63++999 9999957671:EN'QTY+153:2'LI N+64++9999999957794:EN'QTY+153:-1'LIN+65++9999999958821:EN'QTY+153:2'LIN+66+ +9999999970205:EN'QTY+153:1'LOC+162+5023949182579: :9'LIN+67++21326806:EN'QTY +153:1'LIN+68++9999999923492:EN'QTY+153:3'LIN+69++ 9999999929029:EN'QTY+153:1 'LIN+70++9999999929951:EN'QTY+153:-1'LIN+71++9999999932234:EN'QTY+153:2'LIN+ 72++9999999937482:EN'QTY+153:2'LIN+73++99999999385 57:EN'QTY+153:3'LIN+74++99 99999943353:EN'QTY+153:1'LIN+75++9999999951419:EN' QTY+153:1'LIN+76++99999999 52942:EN'QTY+153:2'LIN+77++9999999953079:EN'QTY+15 3:6'LIN+78++9999999953147: EN'QTY+153:1'LIN+79++9999999953154:EN'QTY+153:1'LI N+80++9999999953253:EN'QTY +153:1'LIN+81++9999999953260:EN'QTY+153:4'LIN+82++ 9999999953277:EN'QTY+153:1 'LIN+83++9999999953307:EN'QTY+153:1'LIN+84++999999 9953338:EN'QTY+153:2'LIN+8 5++9999999953352:EN'QTY+153:2'LIN+86++999999995388 8:EN'QTY+153:-3'LIN+87++99 99999954267:EN'QTY+153:3'LIN+88++9999999954328:EN' QTY+153:1'LIN+89++99999999 56384:EN'QTY+153:5'LIN+90++9999999956391:EN'QTY+15 3:9'LIN+91++9999999956407: EN'QTY+153:3'LIN+92++9999999957015:EN'QTY+153:8'LI N+93++9999999957022:EN'QTY +153:4'LIN+94++9999999957251:EN'QTY+153:-2'LIN+95++9999999957473:EN'QTY+153: 1'LIN+96++9999999957619:EN'QTY+153:1'LIN+97++99999 99957657:EN'QTY+153:-2'LIN +98++9999999957695:EN'QTY+153:1'LIN+99++9999999957 732:EN'QTY+153:1'LIN+100++ 9999999957749:EN'QTY+153:6'LIN+101++9999999957886: EN'QTY+153:2'LIN+102++9999 999957985:EN'QTY+153:5'LIN+103++9999999958005:EN'Q TY+153:1'LIN+104++99999999 58821:EN'QTY+153:8'LIN+105++9999999960657:EN'QTY+1 53:-1'LIN+106++99999999616 16:EN'QTY+153:2'LIN+107++9999999970199:EN'QTY+153: 6'LIN+108++9999999970205:E N'QTY+153:5'LIN+109++9999999970557:EN'QTY+153:2'LO C+162+5023949223920::9'LIN +110++5014838064023:EN'QTY+153:3'LIN+111++99999999 29029:EN'QTY+153:1'LIN+112 ++9999999929951:EN'QTY+153:2'LIN+113++999999992996 8:EN'QTY+153:-1'LIN+114++9 999999938557:EN'QTY+153:1'LIN+115++9999999949560:E N'QTY+153:2'LIN+116++99999 99953154:EN'QTY+153:4'LIN+117++9999999953253:EN'QT Y+153:3'LIN+118++999999995 3284:EN'QTY+153:1'LIN+119++9999999953352:EN'QTY+15 3:1'LIN+120++9999999953390 :EN'QTY+153:1'LIN+121++9999999953888:EN'QTY+153:3' LIN+122++9999999956384:EN' QTY+153:5'LIN+123++9999999956391:EN'QTY+153:14'LIN +124++9999999956407:EN'QTY +153:2'LIN+125++9999999957015:EN'QTY+153:3'LIN+126 ++9999999957183:EN'QTY+153 :3'LIN+127++9999999957251:EN'QTY+153:2'LIN+128++99 99999957671:EN'QTY+153:4'L IN+129++9999999957688:EN'QTY+153:2'LIN+130++999999 9957701:EN'QTY+153:-1'LIN+ 131++9999999957794:EN'QTY+153:2'LIN+132++999999995 7985:EN'QTY+153:1'LIN+133+ +9999999961616:EN'QTY+153:7'LIN+134++9999999970199 :EN'QTY+153:1'LOC+162+5023 949232199::9'LIN+135++9999999923492:EN'QTY+153:1'L IN+136++9999999951419:EN'Q TY+153:2'LIN+137++9999999953147:EN'QTY+153:-1'LIN+138++9999999953253:EN'QTY+ 153:8'LIN+139++9999999953260:EN'QTY+153:3'LIN+140+ +9999999953277:EN'QTY+153: 2'LIN+141++9999999953345:EN'QTY+153:1'LIN+142++999 9999953888:EN'QTY+153:2'LI N+143++9999999956384:EN'QTY+153:3'LIN+144++9999999 956391:EN'QTY+153:1'LIN+14 5++9999999956407:EN'QTY+153:1'LIN+146++99999999570 15:EN'QTY+153:1'LIN+147++9 999999957022:EN'QTY+153:4'LIN+148++9999999957749:E N'QTY+153:3'LIN+149++99999 99957909:EN'QTY+153:2'LIN+150++9999999957985:EN'QT Y+153:2'LIN+151++999999997 0540:EN'QTY+153:2'UNT+1608+22'UNZ+1+435' |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Importing Long String - String Manipulation (EDI EANCOM 96a)
Sub testme2()
Dim FName As String Dim FNum As Long Dim l As String Dim l1 As Variant Dim s As String Dim sChr As String Dim rng1 As Range, rng As Range Dim cell As Range, iloc As Long Columns("A:D").ClearContents Columns(3).NumberFormat = _ "0000000000000" FName = "C:\SLSRPT.txt" FNum = FreeFile Open FName For Input As FNum Line Input #FNum, s s = Application.Clean(s) s = Replace(s, Chr(9), "") l = s l = Replace(l, "LIN+", "LIN+,") l = Replace(l, "LOC", "LIN+LOC") l = Replace(l, ":EN'QTY+153:", ",") l = Replace(l, "'", "") ' l = Replace(l, "+", ",") l1 = Split(l, "LIN+") Cells(1, 1).Resize(UBound(l1) - _ LBound(l1) + 1).Value = Application. _ Transpose(l1) Close #FNum Rows(1).Delete Columns(1).Replace "++", "," Columns(1).TextToColumns _ Destination:=Range("A1"), _ DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, _ ConsecutiveDelimiter:=False, _ Tab:=False, _ Semicolon:=False, _ Comma:=True, _ Space:=False, _ Other:=False, _ FieldInfo:=Array( _ Array(1, 1), _ Array(2, 1), _ Array(3, 1), _ Array(4, 1)) Set rng1 = Cells(Rows.Count, 4).End(xlUp) iloc = InStr(1, rng1, "UN", vbTextCompare) rng1 = Left(rng1, iloc - 1) Set rng = Columns(1).SpecialCells(xlConstants) For Each cell In rng iloc = InStr(1, cell, "+", vbTextCompare) iloc = InStr(iloc + 1, cell, "+", vbTextCompare) cell.Value = "'" & Mid(cell, iloc + 1, 13) Next Set rng = Columns(1).SpecialCells(xlBlanks) rng.Formula = "=" & rng(1).Offset(-1, 0).Address(0, 0) Set rng = Range(Cells(1, 1), _ Cells(Rows.Count, 1).End(xlUp)) rng.Formula = rng.Value Set rng = Columns(2).SpecialCells(xlBlanks) rng.EntireRow.Delete Columns(2).Delete Rows(1).Insert Range("A1:C1").Value = _ Array("STOREID", "EAN", "QTY153") Columns("A:C").AutoFit Range("A1").CurrentRegion.Name = "Database" End Sub should produce what you describe. -- Regards, Tom Ogilvy "Brian" wrote in message ... That worked perfectly!,. however, what i thought i wanted wasnt quite right... If I wanted to get into into a format like: STOREID EAN QTY153 5023949049625 5014838132234 1 5023949049625 5014838153277 -1 5023949049625 5014838156407 1 5023949049625 5014838170212 2 So i only ended up with 3 columns (ditching the LIN no). I would also want to automatically add the headings at A1:C1 For a pretty readabl;e report I decided to pivot the data, which works very nicely for the user, only I had to copy the STOREID along side every EAN entry. I was hoping that looking through an example formula would make it much clearer exactly how it works.... I have been fiddling, but not been sucessful as yet. Any chance you could add some comments to your snippet which might help me... Thanks for all your help, i have actually got the string into a readable pivot, just like to automate that last bit, before i look at the next part of these reports! Brian "Tom Ogilvy" wrote in message ... Sub testme2() Dim FName As String Dim FNum As Long Dim l As String Dim l1 As Variant Dim s As String Dim sChr As String Dim rng1 As Range, rng As Range Dim cell As Range, iloc As Long Columns("A:D").ClearContents Columns(3).NumberFormat = _ "0000000000000" FName = "C:\SLSRPT.txt" FNum = FreeFile Open FName For Input As FNum Line Input #FNum, s s = Application.Clean(s) s = Replace(s, Chr(9), "") l = s l = Replace(l, "LIN+", "LIN+,") l = Replace(l, "LOC", "LIN+LOC") l = Replace(l, ":EN'QTY+153:", ",") l = Replace(l, "'", "") ' l = Replace(l, "+", ",") l1 = Split(l, "LIN+") Cells(1, 1).Resize(UBound(l1) - _ LBound(l1) + 1).Value = Application. _ Transpose(l1) Close #FNum Rows(1).Delete Columns(1).Replace "++", "," Columns(1).TextToColumns _ Destination:=Range("A1"), _ DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, _ ConsecutiveDelimiter:=False, _ Tab:=False, _ Semicolon:=False, _ Comma:=True, _ Space:=False, _ Other:=False, _ FieldInfo:=Array( _ Array(1, 1), _ Array(2, 1), _ Array(3, 1), _ Array(4, 1)) Set rng1 = Cells(Rows.Count, 4).End(xlUp) iloc = InStr(1, rng1, "UN", vbTextCompare) rng1 = Left(rng1, iloc - 1) Set rng = Columns(1).SpecialCells(xlConstants) For Each cell In rng iloc = InStr(1, cell, "+", vbTextCompare) iloc = InStr(iloc + 1, cell, "+", vbTextCompare) cell.Value = "'" & Mid(cell, iloc + 1, 13) Next End Sub -- Regards, Tom Ogilvy "Brian" wrote in message ... Thanks Tom, thats fantastic! The first line I would basically delete (the information will not be used at this time).. Basically the LOC is a separator for the StoreID followed by all the LIN orders until the next LOC. I would like to get it so i get the (STOREID) Column1 9999999988888 (LINE) (PRODUCTID) (QTY) Column2 Column3 Column4 1 9999999912345 1 2 9999999923456 2 3 9999999934567 1 4 9999999954321 3 column 1 would be the line, column 2 the ProductID and column 3 the Qty It would be good if i could separate the lines by LOC so i get Brian "Tom Ogilvy" wrote in message ... Here is a start. You would have to tell me what to do with the first line and what the LOC lines are. Sub testme2() Dim FName As String Dim FNum As Long Dim l As String Dim l1 As Variant FName = "C:\SLSRPT.txt" FNum = FreeFile Open FName For Input As FNum Line Input #FNum, l l1 = Split(l, "LIN+") Cells(1, 1).Resize(UBound(l1) - _ LBound(l1) + 1).Value = Application. _ Transpose(l1) Close #FNum End Sub -- regards, Tom Ogilvy "Brian" wrote in message ... I have a long text file , some of you may recognise it as a EDI EANCOM 96a file... Basically, this file is sent via EDI, but is not imported into our systems. It is purely used to get sales figures 'LIN+1++9999999932234:EN'QTY+153:1 each line is prefixed with 'LIN+ 'LOC+162+9999999949625::9 will give me the location However the file is one long string, not CSV etc so i have to find a way to get the imported file to split into rows at each 'LIN+ i have managed to get the text to import, but all in one cell, or split into the world allocation of cells... I would like to be able to get the file to dump into a sheet as follows LOC 9999999949625 LIN STORE QTY 153 QTY 200 QTY 301 1 999999932234 1 2 9999999953277 1 3 9999999956407 1 4 9999999970212 2 Note i also recieve an Inventory File that has a similar format (QTY 200, 301 etc ), so i want to import that at some point to... Once i get the idea on the best wat to split the file with one QTY i will look at the bigger one... As you can see from the text extract below of the file , its not fixed width of very easy to split.... THIS DONT WORK... ________________________________________ Sub testme2() Dim FName As String Dim KeepTheNext As Boolean Dim FNum As Long Dim i As Long Dim sLine As String FName = "C:\SLSRPT.txt" FNum = FreeFile Open FName For Input As FNum i = 1 KeepTheNext = False Do While Not EOF(FNum) Input #FNum, sLine If InStr(1, sLine, "'LIN+", vbTextCompare) 0 Then KeepTheNext = True Else If KeepTheNext = True Then KeepTheNext = False Cells(i, 1).Value = sLine ' Exit Do i = i + 1 End If End If Loop End Sub HELP!!!!! Thanks for any advice in advance Brian ________________________________________ SAMPLE OF TEXT.... UNB+UNOA:3+999999990004:14+9999999900001+060205:05 08+435+ +SLSRPT'UNH+22+SLSRPT:D:96A:UN:EAN008'BGM+73E::9+0 0000014+9'DTM+356:20060129 -20060204:718'NAD+FR+9999999900004::9'NAD+SU+999999 9900001::9'LOC+162+999 9 99 9949625::9'LIN+1++9999999932234:EN'QTY+153:1'LIN+2 ++9999999953277:EN'QTY+153 :-1'LIN+3++9999999956407:EN'QTY+153:1'LIN+4++9999999 970212:EN'QTY+153:2'LOC+ 162+5023949057895::9'LIN+5++21481505:EN'QTY+153:1' LIN+6++9999999923492:EN'QT Y+153:-1'LIN+7++9999999929029:EN'QTY+153:2'LIN+8++9999999 932418:EN'QTY+153:1 'LIN+9++9999999937482:EN'QTY+153:1'LIN+10++9999999 949560:EN'QTY+153:-1'LIN+1 1++9999999953253:EN'QTY+153:2'LIN+12++999999995388 8:EN'QTY+153:3'LIN+13++999 9999954205:EN'QTY+153:2'LIN+14++9999999954250:EN'Q TY+153:1'LIN+15++999999995 6407:EN'QTY+153:1'LIN+16++9999999957015:EN'QTY+153 :7'LIN+17++9999999957022:E N'QTY+153:2'LIN+18++9999999957473:EN'QTY+153:1'LIN +19++9999999957688:EN'QTY+ 153:3'LIN+20++9999999957794:EN'QTY+153:2'LIN+21++9 999999970496:EN'QTY+153:3' LOC+162+5023949136774::9'LIN+22++21326806:EN'QTY+1 53:1'LIN+23++9999999923492 :EN'QTY+153:2'LIN+24++9999999929029:EN'QTY+153:1'L IN+25++9999999932418:EN'QT Y+153:2'LIN+26++9999999953253:EN'QTY+153:1'LIN+27+ +9999999953260:EN'QTY+153: 1'LIN+28++9999999953284:EN'QTY+153:1'LIN+29++99999 99953307:EN'QTY+153:1'LIN+ 30++9999999953338:EN'QTY+153:3'LIN+31++99999999533 45:EN'QTY+153:3'LIN+32++99 99999953383:EN'QTY+153:-1'LIN+33++9999999953888:EN'QTY+153:7'LIN+34++99999 99 956391:EN'QTY+153:1'LIN+35++9999999956407:EN'QTY+1 53:1'LIN+36++9999999957015 :EN'QTY+153:2'LIN+37++9999999957022:EN'QTY+153:1'L IN+38++9999999957183:EN'QT Y+153:1'LIN+39++9999999957688:EN'QTY+153:2'LIN+40+ +9999999957695:EN'QTY+153: 1'LIN+41++9999999957749:EN'QTY+153:7'LIN+42++99999 99957794:EN'QTY+153:10'LIN +43++9999999957879:EN'QTY+153:1'LIN+44++9999999957 985:EN'QTY+153:1'LIN+45++9 999999958821:EN'QTY+153:-1'LIN+46++9999999961616:EN'QTY+153:3'LIN+47++99999 9 9970199:EN'QTY+153:4'LIN+48++9999999970205:EN'QTY+ 153:2'LIN+49++999999997021 2:EN'QTY+153:5'LIN+50++9999999970649:EN'QTY+153:2' LOC+162+5023949145040::9'L IN+51++5014838064023:EN'QTY+153:3'LIN+52++99999999 23492:EN'QTY+153:1'LIN+53+ +9999999929029:EN'QTY+153:1'LIN+54++9999999932234: EN'QTY+153:3'LIN+55++99999 99938557:EN'QTY+153:1'LIN+56++9999999953253:EN'QTY +153:2'LIN+57++99999999532 77:EN'QTY+153:2'LIN+58++9999999953284:EN'QTY+153:1 'LIN+59++9999999953888:EN' QTY+153:3'LIN+60++9999999954281:EN'QTY+153:1'LIN+6 1++9999999957015:EN'QTY+15 3:6'LIN+62++9999999957022:EN'QTY+153:5'LIN+63++999 9999957671:EN'QTY+153:2'LI N+64++9999999957794:EN'QTY+153:-1'LIN+65++9999999958821:EN'QTY+153:2'LIN+66+ +9999999970205:EN'QTY+153:1'LOC+162+5023949182579: :9'LIN+67++21326806:EN'QTY +153:1'LIN+68++9999999923492:EN'QTY+153:3'LIN+69++ 9999999929029:EN'QTY+153:1 'LIN+70++9999999929951:EN'QTY+153:-1'LIN+71++9999999932234:EN'QTY+153:2'LIN+ 72++9999999937482:EN'QTY+153:2'LIN+73++99999999385 57:EN'QTY+153:3'LIN+74++99 99999943353:EN'QTY+153:1'LIN+75++9999999951419:EN' QTY+153:1'LIN+76++99999999 52942:EN'QTY+153:2'LIN+77++9999999953079:EN'QTY+15 3:6'LIN+78++9999999953147: EN'QTY+153:1'LIN+79++9999999953154:EN'QTY+153:1'LI N+80++9999999953253:EN'QTY +153:1'LIN+81++9999999953260:EN'QTY+153:4'LIN+82++ 9999999953277:EN'QTY+153:1 'LIN+83++9999999953307:EN'QTY+153:1'LIN+84++999999 9953338:EN'QTY+153:2'LIN+8 5++9999999953352:EN'QTY+153:2'LIN+86++999999995388 8:EN'QTY+153:-3'LIN+87++99 99999954267:EN'QTY+153:3'LIN+88++9999999954328:EN' QTY+153:1'LIN+89++99999999 56384:EN'QTY+153:5'LIN+90++9999999956391:EN'QTY+15 3:9'LIN+91++9999999956407: EN'QTY+153:3'LIN+92++9999999957015:EN'QTY+153:8'LI N+93++9999999957022:EN'QTY +153:4'LIN+94++9999999957251:EN'QTY+153:-2'LIN+95++9999999957473:EN'QTY+153: 1'LIN+96++9999999957619:EN'QTY+153:1'LIN+97++99999 99957657:EN'QTY+153:-2'LIN +98++9999999957695:EN'QTY+153:1'LIN+99++9999999957 732:EN'QTY+153:1'LIN+100++ 9999999957749:EN'QTY+153:6'LIN+101++9999999957886: EN'QTY+153:2'LIN+102++9999 999957985:EN'QTY+153:5'LIN+103++9999999958005:EN'Q TY+153:1'LIN+104++99999999 58821:EN'QTY+153:8'LIN+105++9999999960657:EN'QTY+1 53:-1'LIN+106++99999999616 16:EN'QTY+153:2'LIN+107++9999999970199:EN'QTY+153: 6'LIN+108++9999999970205:E N'QTY+153:5'LIN+109++9999999970557:EN'QTY+153:2'LO C+162+5023949223920::9'LIN +110++5014838064023:EN'QTY+153:3'LIN+111++99999999 29029:EN'QTY+153:1'LIN+112 ++9999999929951:EN'QTY+153:2'LIN+113++999999992996 8:EN'QTY+153:-1'LIN+114++9 999999938557:EN'QTY+153:1'LIN+115++9999999949560:E N'QTY+153:2'LIN+116++99999 99953154:EN'QTY+153:4'LIN+117++9999999953253:EN'QT Y+153:3'LIN+118++999999995 3284:EN'QTY+153:1'LIN+119++9999999953352:EN'QTY+15 3:1'LIN+120++9999999953390 :EN'QTY+153:1'LIN+121++9999999953888:EN'QTY+153:3' LIN+122++9999999956384:EN' QTY+153:5'LIN+123++9999999956391:EN'QTY+153:14'LIN +124++9999999956407:EN'QTY +153:2'LIN+125++9999999957015:EN'QTY+153:3'LIN+126 ++9999999957183:EN'QTY+153 :3'LIN+127++9999999957251:EN'QTY+153:2'LIN+128++99 99999957671:EN'QTY+153:4'L IN+129++9999999957688:EN'QTY+153:2'LIN+130++999999 9957701:EN'QTY+153:-1'LIN+ 131++9999999957794:EN'QTY+153:2'LIN+132++999999995 7985:EN'QTY+153:1'LIN+133+ +9999999961616:EN'QTY+153:7'LIN+134++9999999970199 :EN'QTY+153:1'LOC+162+5023 949232199::9'LIN+135++9999999923492:EN'QTY+153:1'L IN+136++9999999951419:EN'Q TY+153:2'LIN+137++9999999953147:EN'QTY+153:-1'LIN+138++9999999953253:EN'QTY+ 153:8'LIN+139++9999999953260:EN'QTY+153:3'LIN+140+ +9999999953277:EN'QTY+153: 2'LIN+141++9999999953345:EN'QTY+153:1'LIN+142++999 9999953888:EN'QTY+153:2'LI N+143++9999999956384:EN'QTY+153:3'LIN+144++9999999 956391:EN'QTY+153:1'LIN+14 5++9999999956407:EN'QTY+153:1'LIN+146++99999999570 15:EN'QTY+153:1'LIN+147++9 999999957022:EN'QTY+153:4'LIN+148++9999999957749:E N'QTY+153:3'LIN+149++99999 99957909:EN'QTY+153:2'LIN+150++9999999957985:EN'QT Y+153:2'LIN+151++999999997 0540:EN'QTY+153:2'UNT+1608+22'UNZ+1+435' |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Importing Long String - String Manipulation (EDI EANCOM 96a)
Thank you so much Tom, your help is greatly appreciated.
I now have the text imported and a Pivot table which displays the information to the user, also have lookups to make those code numbers display as actually useful products and sites.... I have posted another very similar problem for the inventry file i am being sent... If you take a look at that one i would be eternally grateful! I am learning from your example but i am afraid I find these arrays beyond my vba skills. Thanks again for your assistance. Brian "Tom Ogilvy" wrote in message ... Sub testme2() Dim FName As String Dim FNum As Long Dim l As String Dim l1 As Variant Dim s As String Dim sChr As String Dim rng1 As Range, rng As Range Dim cell As Range, iloc As Long Columns("A:D").ClearContents Columns(3).NumberFormat = _ "0000000000000" FName = "C:\SLSRPT.txt" FNum = FreeFile Open FName For Input As FNum Line Input #FNum, s s = Application.Clean(s) s = Replace(s, Chr(9), "") l = s l = Replace(l, "LIN+", "LIN+,") l = Replace(l, "LOC", "LIN+LOC") l = Replace(l, ":EN'QTY+153:", ",") l = Replace(l, "'", "") ' l = Replace(l, "+", ",") l1 = Split(l, "LIN+") Cells(1, 1).Resize(UBound(l1) - _ LBound(l1) + 1).Value = Application. _ Transpose(l1) Close #FNum Rows(1).Delete Columns(1).Replace "++", "," Columns(1).TextToColumns _ Destination:=Range("A1"), _ DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, _ ConsecutiveDelimiter:=False, _ Tab:=False, _ Semicolon:=False, _ Comma:=True, _ Space:=False, _ Other:=False, _ FieldInfo:=Array( _ Array(1, 1), _ Array(2, 1), _ Array(3, 1), _ Array(4, 1)) Set rng1 = Cells(Rows.Count, 4).End(xlUp) iloc = InStr(1, rng1, "UN", vbTextCompare) rng1 = Left(rng1, iloc - 1) Set rng = Columns(1).SpecialCells(xlConstants) For Each cell In rng iloc = InStr(1, cell, "+", vbTextCompare) iloc = InStr(iloc + 1, cell, "+", vbTextCompare) cell.Value = "'" & Mid(cell, iloc + 1, 13) Next Set rng = Columns(1).SpecialCells(xlBlanks) rng.Formula = "=" & rng(1).Offset(-1, 0).Address(0, 0) Set rng = Range(Cells(1, 1), _ Cells(Rows.Count, 1).End(xlUp)) rng.Formula = rng.Value Set rng = Columns(2).SpecialCells(xlBlanks) rng.EntireRow.Delete Columns(2).Delete Rows(1).Insert Range("A1:C1").Value = _ Array("STOREID", "EAN", "QTY153") Columns("A:C").AutoFit Range("A1").CurrentRegion.Name = "Database" End Sub should produce what you describe. -- Regards, Tom Ogilvy "Brian" wrote in message ... That worked perfectly!,. however, what i thought i wanted wasnt quite right... If I wanted to get into into a format like: STOREID EAN QTY153 5023949049625 5014838132234 1 5023949049625 5014838153277 -1 5023949049625 5014838156407 1 5023949049625 5014838170212 2 So i only ended up with 3 columns (ditching the LIN no). I would also want to automatically add the headings at A1:C1 For a pretty readabl;e report I decided to pivot the data, which works very nicely for the user, only I had to copy the STOREID along side every EAN entry. I was hoping that looking through an example formula would make it much clearer exactly how it works.... I have been fiddling, but not been sucessful as yet. Any chance you could add some comments to your snippet which might help me... Thanks for all your help, i have actually got the string into a readable pivot, just like to automate that last bit, before i look at the next part of these reports! Brian "Tom Ogilvy" wrote in message ... Sub testme2() Dim FName As String Dim FNum As Long Dim l As String Dim l1 As Variant Dim s As String Dim sChr As String Dim rng1 As Range, rng As Range Dim cell As Range, iloc As Long Columns("A:D").ClearContents Columns(3).NumberFormat = _ "0000000000000" FName = "C:\SLSRPT.txt" FNum = FreeFile Open FName For Input As FNum Line Input #FNum, s s = Application.Clean(s) s = Replace(s, Chr(9), "") l = s l = Replace(l, "LIN+", "LIN+,") l = Replace(l, "LOC", "LIN+LOC") l = Replace(l, ":EN'QTY+153:", ",") l = Replace(l, "'", "") ' l = Replace(l, "+", ",") l1 = Split(l, "LIN+") Cells(1, 1).Resize(UBound(l1) - _ LBound(l1) + 1).Value = Application. _ Transpose(l1) Close #FNum Rows(1).Delete Columns(1).Replace "++", "," Columns(1).TextToColumns _ Destination:=Range("A1"), _ DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, _ ConsecutiveDelimiter:=False, _ Tab:=False, _ Semicolon:=False, _ Comma:=True, _ Space:=False, _ Other:=False, _ FieldInfo:=Array( _ Array(1, 1), _ Array(2, 1), _ Array(3, 1), _ Array(4, 1)) Set rng1 = Cells(Rows.Count, 4).End(xlUp) iloc = InStr(1, rng1, "UN", vbTextCompare) rng1 = Left(rng1, iloc - 1) Set rng = Columns(1).SpecialCells(xlConstants) For Each cell In rng iloc = InStr(1, cell, "+", vbTextCompare) iloc = InStr(iloc + 1, cell, "+", vbTextCompare) cell.Value = "'" & Mid(cell, iloc + 1, 13) Next End Sub -- Regards, Tom Ogilvy "Brian" wrote in message ... Thanks Tom, thats fantastic! The first line I would basically delete (the information will not be used at this time).. Basically the LOC is a separator for the StoreID followed by all the LIN orders until the next LOC. I would like to get it so i get the (STOREID) Column1 9999999988888 (LINE) (PRODUCTID) (QTY) Column2 Column3 Column4 1 9999999912345 1 2 9999999923456 2 3 9999999934567 1 4 9999999954321 3 column 1 would be the line, column 2 the ProductID and column 3 the Qty It would be good if i could separate the lines by LOC so i get Brian "Tom Ogilvy" wrote in message ... Here is a start. You would have to tell me what to do with the first line and what the LOC lines are. Sub testme2() Dim FName As String Dim FNum As Long Dim l As String Dim l1 As Variant FName = "C:\SLSRPT.txt" FNum = FreeFile Open FName For Input As FNum Line Input #FNum, l l1 = Split(l, "LIN+") Cells(1, 1).Resize(UBound(l1) - _ LBound(l1) + 1).Value = Application. _ Transpose(l1) Close #FNum End Sub -- regards, Tom Ogilvy "Brian" wrote in message ... I have a long text file , some of you may recognise it as a EDI EANCOM 96a file... Basically, this file is sent via EDI, but is not imported into our systems. It is purely used to get sales figures 'LIN+1++9999999932234:EN'QTY+153:1 each line is prefixed with 'LIN+ 'LOC+162+9999999949625::9 will give me the location However the file is one long string, not CSV etc so i have to find a way to get the imported file to split into rows at each 'LIN+ i have managed to get the text to import, but all in one cell, or split into the world allocation of cells... I would like to be able to get the file to dump into a sheet as follows LOC 9999999949625 LIN STORE QTY 153 QTY 200 QTY 301 1 999999932234 1 2 9999999953277 1 3 9999999956407 1 4 9999999970212 2 Note i also recieve an Inventory File that has a similar format (QTY 200, 301 etc ), so i want to import that at some point to... Once i get the idea on the best wat to split the file with one QTY i will look at the bigger one... As you can see from the text extract below of the file , its not fixed width of very easy to split.... THIS DONT WORK... ________________________________________ Sub testme2() Dim FName As String Dim KeepTheNext As Boolean Dim FNum As Long Dim i As Long Dim sLine As String FName = "C:\SLSRPT.txt" FNum = FreeFile Open FName For Input As FNum i = 1 KeepTheNext = False Do While Not EOF(FNum) Input #FNum, sLine If InStr(1, sLine, "'LIN+", vbTextCompare) 0 Then KeepTheNext = True Else If KeepTheNext = True Then KeepTheNext = False Cells(i, 1).Value = sLine ' Exit Do i = i + 1 End If End If Loop End Sub HELP!!!!! Thanks for any advice in advance Brian ________________________________________ SAMPLE OF TEXT.... UNB+UNOA:3+999999990004:14+9999999900001+060205:05 08+435+ +SLSRPT'UNH+22+SLSRPT:D:96A:UN:EAN008'BGM+73E::9+0 0000014+9'DTM+356:20060129 -20060204:718'NAD+FR+9999999900004::9'NAD+SU+999999 9900001::9'LOC+162+999 9 99 9949625::9'LIN+1++9999999932234:EN'QTY+153:1'LIN+2 ++9999999953277:EN'QTY+153 :-1'LIN+3++9999999956407:EN'QTY+153:1'LIN+4++9999999 970212:EN'QTY+153:2'LOC+ 162+5023949057895::9'LIN+5++21481505:EN'QTY+153:1' LIN+6++9999999923492:EN'QT Y+153:-1'LIN+7++9999999929029:EN'QTY+153:2'LIN+8++9999999 932418:EN'QTY+153:1 'LIN+9++9999999937482:EN'QTY+153:1'LIN+10++9999999 949560:EN'QTY+153:-1'LIN+1 1++9999999953253:EN'QTY+153:2'LIN+12++999999995388 8:EN'QTY+153:3'LIN+13++999 9999954205:EN'QTY+153:2'LIN+14++9999999954250:EN'Q TY+153:1'LIN+15++999999995 6407:EN'QTY+153:1'LIN+16++9999999957015:EN'QTY+153 :7'LIN+17++9999999957022:E N'QTY+153:2'LIN+18++9999999957473:EN'QTY+153:1'LIN +19++9999999957688:EN'QTY+ 153:3'LIN+20++9999999957794:EN'QTY+153:2'LIN+21++9 999999970496:EN'QTY+153:3' LOC+162+5023949136774::9'LIN+22++21326806:EN'QTY+1 53:1'LIN+23++9999999923492 :EN'QTY+153:2'LIN+24++9999999929029:EN'QTY+153:1'L IN+25++9999999932418:EN'QT Y+153:2'LIN+26++9999999953253:EN'QTY+153:1'LIN+27+ +9999999953260:EN'QTY+153: 1'LIN+28++9999999953284:EN'QTY+153:1'LIN+29++99999 99953307:EN'QTY+153:1'LIN+ 30++9999999953338:EN'QTY+153:3'LIN+31++99999999533 45:EN'QTY+153:3'LIN+32++99 99999953383:EN'QTY+153:-1'LIN+33++9999999953888:EN'QTY+153:7'LIN+34++99999 99 956391:EN'QTY+153:1'LIN+35++9999999956407:EN'QTY+1 53:1'LIN+36++9999999957015 :EN'QTY+153:2'LIN+37++9999999957022:EN'QTY+153:1'L IN+38++9999999957183:EN'QT Y+153:1'LIN+39++9999999957688:EN'QTY+153:2'LIN+40+ +9999999957695:EN'QTY+153: 1'LIN+41++9999999957749:EN'QTY+153:7'LIN+42++99999 99957794:EN'QTY+153:10'LIN +43++9999999957879:EN'QTY+153:1'LIN+44++9999999957 985:EN'QTY+153:1'LIN+45++9 999999958821:EN'QTY+153:-1'LIN+46++9999999961616:EN'QTY+153:3'LIN+47++99999 9 9970199:EN'QTY+153:4'LIN+48++9999999970205:EN'QTY+ 153:2'LIN+49++999999997021 2:EN'QTY+153:5'LIN+50++9999999970649:EN'QTY+153:2' LOC+162+5023949145040::9'L IN+51++5014838064023:EN'QTY+153:3'LIN+52++99999999 23492:EN'QTY+153:1'LIN+53+ +9999999929029:EN'QTY+153:1'LIN+54++9999999932234: EN'QTY+153:3'LIN+55++99999 99938557:EN'QTY+153:1'LIN+56++9999999953253:EN'QTY +153:2'LIN+57++99999999532 77:EN'QTY+153:2'LIN+58++9999999953284:EN'QTY+153:1 'LIN+59++9999999953888:EN' QTY+153:3'LIN+60++9999999954281:EN'QTY+153:1'LIN+6 1++9999999957015:EN'QTY+15 3:6'LIN+62++9999999957022:EN'QTY+153:5'LIN+63++999 9999957671:EN'QTY+153:2'LI N+64++9999999957794:EN'QTY+153:-1'LIN+65++9999999958821:EN'QTY+153:2'LIN+66+ +9999999970205:EN'QTY+153:1'LOC+162+5023949182579: :9'LIN+67++21326806:EN'QTY +153:1'LIN+68++9999999923492:EN'QTY+153:3'LIN+69++ 9999999929029:EN'QTY+153:1 'LIN+70++9999999929951:EN'QTY+153:-1'LIN+71++9999999932234:EN'QTY+153:2'LIN+ 72++9999999937482:EN'QTY+153:2'LIN+73++99999999385 57:EN'QTY+153:3'LIN+74++99 99999943353:EN'QTY+153:1'LIN+75++9999999951419:EN' QTY+153:1'LIN+76++99999999 52942:EN'QTY+153:2'LIN+77++9999999953079:EN'QTY+15 3:6'LIN+78++9999999953147: EN'QTY+153:1'LIN+79++9999999953154:EN'QTY+153:1'LI N+80++9999999953253:EN'QTY +153:1'LIN+81++9999999953260:EN'QTY+153:4'LIN+82++ 9999999953277:EN'QTY+153:1 'LIN+83++9999999953307:EN'QTY+153:1'LIN+84++999999 9953338:EN'QTY+153:2'LIN+8 5++9999999953352:EN'QTY+153:2'LIN+86++999999995388 8:EN'QTY+153:-3'LIN+87++99 99999954267:EN'QTY+153:3'LIN+88++9999999954328:EN' QTY+153:1'LIN+89++99999999 56384:EN'QTY+153:5'LIN+90++9999999956391:EN'QTY+15 3:9'LIN+91++9999999956407: EN'QTY+153:3'LIN+92++9999999957015:EN'QTY+153:8'LI N+93++9999999957022:EN'QTY +153:4'LIN+94++9999999957251:EN'QTY+153:-2'LIN+95++9999999957473:EN'QTY+153: 1'LIN+96++9999999957619:EN'QTY+153:1'LIN+97++99999 99957657:EN'QTY+153:-2'LIN +98++9999999957695:EN'QTY+153:1'LIN+99++9999999957 732:EN'QTY+153:1'LIN+100++ 9999999957749:EN'QTY+153:6'LIN+101++9999999957886: EN'QTY+153:2'LIN+102++9999 999957985:EN'QTY+153:5'LIN+103++9999999958005:EN'Q TY+153:1'LIN+104++99999999 58821:EN'QTY+153:8'LIN+105++9999999960657:EN'QTY+1 53:-1'LIN+106++99999999616 16:EN'QTY+153:2'LIN+107++9999999970199:EN'QTY+153: 6'LIN+108++9999999970205:E N'QTY+153:5'LIN+109++9999999970557:EN'QTY+153:2'LO C+162+5023949223920::9'LIN +110++5014838064023:EN'QTY+153:3'LIN+111++99999999 29029:EN'QTY+153:1'LIN+112 ++9999999929951:EN'QTY+153:2'LIN+113++999999992996 8:EN'QTY+153:-1'LIN+114++9 999999938557:EN'QTY+153:1'LIN+115++9999999949560:E N'QTY+153:2'LIN+116++99999 99953154:EN'QTY+153:4'LIN+117++9999999953253:EN'QT Y+153:3'LIN+118++999999995 3284:EN'QTY+153:1'LIN+119++9999999953352:EN'QTY+15 3:1'LIN+120++9999999953390 :EN'QTY+153:1'LIN+121++9999999953888:EN'QTY+153:3' LIN+122++9999999956384:EN' QTY+153:5'LIN+123++9999999956391:EN'QTY+153:14'LIN +124++9999999956407:EN'QTY +153:2'LIN+125++9999999957015:EN'QTY+153:3'LIN+126 ++9999999957183:EN'QTY+153 :3'LIN+127++9999999957251:EN'QTY+153:2'LIN+128++99 99999957671:EN'QTY+153:4'L IN+129++9999999957688:EN'QTY+153:2'LIN+130++999999 9957701:EN'QTY+153:-1'LIN+ 131++9999999957794:EN'QTY+153:2'LIN+132++999999995 7985:EN'QTY+153:1'LIN+133+ +9999999961616:EN'QTY+153:7'LIN+134++9999999970199 :EN'QTY+153:1'LOC+162+5023 949232199::9'LIN+135++9999999923492:EN'QTY+153:1'L IN+136++9999999951419:EN'Q TY+153:2'LIN+137++9999999953147:EN'QTY+153:-1'LIN+138++9999999953253:EN'QTY+ 153:8'LIN+139++9999999953260:EN'QTY+153:3'LIN+140+ +9999999953277:EN'QTY+153: 2'LIN+141++9999999953345:EN'QTY+153:1'LIN+142++999 9999953888:EN'QTY+153:2'LI N+143++9999999956384:EN'QTY+153:3'LIN+144++9999999 956391:EN'QTY+153:1'LIN+14 5++9999999956407:EN'QTY+153:1'LIN+146++99999999570 15:EN'QTY+153:1'LIN+147++9 999999957022:EN'QTY+153:4'LIN+148++9999999957749:E N'QTY+153:3'LIN+149++99999 99957909:EN'QTY+153:2'LIN+150++9999999957985:EN'QT Y+153:2'LIN+151++999999997 0540:EN'QTY+153:2'UNT+1608+22'UNZ+1+435' |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| String Manipulation | Excel Discussion (Misc queries) | |||
| String manipulation!! | Excel Programming | |||
| string manipulation | Excel Programming | |||
| VBA String manipulation | Excel Programming | |||
| string manipulation | Excel Programming | |||
Linear Mode
