Hiya folks.
I have a template being output from our ERP system, a text ASCII file which can vary in number of fields depending on which template is used. I am using the ^ character to separate the fields. I know I can open the text file into excel and use the option to open into excel and look for ^ to split the info, however I am struggling to save back the file into a format which will allow upload back to the system. Hopefully below will explain.
The is output from system into a text doc, which I can copy paste into Sheet1 A1
I^0-02-0100100^AUK01^12^3^52^3^2^BNT^3^30^0^2
So 13 fields separated by ^
So in sheet 2, I would like to have a formula which output as per below
A1 = I
B1 = 0-02-0100100
C1 = AUK01
D1= 12
E1 = 3 - I would like to amend this value to 9
F1 = 52
G1 = 3
H1 = 2
I1 = BNT - I would like to amend this value to MRP
J1 = 3
K1 = 30
L1 = 0 - I would like to amend this value to 4
N1 = 2
then on sheet 3, would like to put it all back together again so I can copy paste back into text doc and upload the changes. so in CELL A1 it would return
I^0-02-0100100^AUK01^12^9^52^3^2^MRP^3^30^4^2
is this at all possible either with formula or maybe VBA?
hopefully this is possible, however i think this is very difficult.
Many Thanks guys!!!!!
I have a template being output from our ERP system, a text ASCII file which can vary in number of fields depending on which template is used. I am using the ^ character to separate the fields. I know I can open the text file into excel and use the option to open into excel and look for ^ to split the info, however I am struggling to save back the file into a format which will allow upload back to the system. Hopefully below will explain.
The is output from system into a text doc, which I can copy paste into Sheet1 A1
I^0-02-0100100^AUK01^12^3^52^3^2^BNT^3^30^0^2
So 13 fields separated by ^
So in sheet 2, I would like to have a formula which output as per below
A1 = I
B1 = 0-02-0100100
C1 = AUK01
D1= 12
E1 = 3 - I would like to amend this value to 9
F1 = 52
G1 = 3
H1 = 2
I1 = BNT - I would like to amend this value to MRP
J1 = 3
K1 = 30
L1 = 0 - I would like to amend this value to 4
N1 = 2
then on sheet 3, would like to put it all back together again so I can copy paste back into text doc and upload the changes. so in CELL A1 it would return
I^0-02-0100100^AUK01^12^9^52^3^2^MRP^3^30^4^2
is this at all possible either with formula or maybe VBA?
hopefully this is possible, however i think this is very difficult.
Many Thanks guys!!!!!