Conversion of text files to Excel in consideration of criterion

Dear Excel experts,
We have 2 different text files
Pasting as it is in a excel file (named data) in 2 different worksheets..
1. (1) EAL_as it is
2. (2) System_as it is
From these 2 txt files, we converting these 2 different lists in same excel file (named data) in another s 2 different worksheets..
3. (1) EAL_List
4. (2) System_list
Criteria :
>> Conversion (text to column) of an excel file named (1) EAL_List from (1) EAL_as it is considering following…
a. Data is starts from the row, where 1st three characters are CTR (because HDRADVANCEUSC ALB H00451 E is optional, not in all cases, hence data start either from 1st or 2nd row depends availability of CTR at first)
b. List is to be commenced from the Container No. (length = 11) , which is at after CTR (length = 3) and space(length = 6) ; in this case 1st row in Container No. column it is GLDU5267198
c. Next field is Weight (length = 5), which is at / immediate after Container No. ; in this case 1st row in Weight column it is 22.30.
d. Next field is ISO (length = 4) which is at / immediate after Weight. ; in this case 1st row in ISO column it is 2210.
e. Next field is Status (length = 1) which is at / immediate after ISO ; in this case 1st row in Status column it is F.
f. Next field is IMO (length = 6) which is at / immediate after Status; in this case 1st row in IMO column it is Blank(NIL).
g. Next field is POL (length = 3) which is at / immediate after IMO; in this case 1st row in POL column it is NSA.
h. Next field is POD (length = 3) which is at / immediate after POL and space (length = 2) ; in this case 1st row in POD column it is JED.
i. Next field is CAT (length = 1) which is at / immediate after POD; in this case 1st row in CAT column it is E.
j. Next field is Mode (length = 1) which is at / immediate after CAT; in this case 1st row in Mode column it is T.
k. Next field is Line (length = 3) which is at / immediate after Mode; in this case 1st row in Line column it is USC.
l. Next field is Rfr Sts (length = 1) which is at / immediate after Line and space (length = 1); in this case 1st row in Rfr Sts column it is N.
m. Last field is Temp (length = 6) which is at / immediate after Rfr Sts and space (length = 1); in this case 1st row in Temp column it is Blank (NIL).

Conversion of an excel file named (2) System_list from (2) System_as it is considering following…
In worksheet (2) System_as it is Search listed filds/column it is Equipment ID, Wgt-MTn,Type,Sts,IMO,POL,POD,Cat,Inbound Carrier, Line, Temp.


Paste (special) it in worksheet (2) System_list Search listed fields and REPLACE...Equipment ID, Wgt-MTn,Type,Sts,IMO, POL, POD, Cat, Inbound Carrier, Line, Temp WITH Container No., Weight,ISO, Status, IMO, POL, POD, Cat, Mode, Line, Rfr Sts,Temp RESPECTIVELY

Insert Rfr Sts in between Line and Temp column

> Value in Rfr Sts column if Temp is Blank Rfr Sts will N ; Temp is Non-Blank Rfr Sts will Y
> Value in Mode column if Mode is TRUCK it is to be replaced with T and remaining all are to be replaced with R

Please help me in the same

- Ravi

Please ref. to attached files

Dear Sir,

As far as Power query is concerned, i am new, can you please explain in details where we want to paste above code/queries and further process,

And i am not working with NSICT but in a container terminal

- Ravi

Sir,
I am unable to see Power Query addin, unable to find any setting for said add in
Can it be done through macro/vba instead of power query; macro / vba will be more simple and user friendly than power query.

Yes Sir, i am in Nhava Sheva.

- Ravi

Can any one help me to resolve it. Thank you for your help in advance
- Ravi

As it require installation of addin in all the require PCs; in fact this is not possible for us, Excel template with macro script will be better.