Text format to excel format macro

[kvisaria]

I have below text file which I want to convert in excel. But the issue is all the data is not properly aligned. Can someone assist

Account Receivable Date: 30-JUN-2018 18:08
Aged Trial Balance - 7 Buckets by GL# - Page: 1
As of Date: 30-JUN-2018



NOT YET CURRENT PAST DUE PAST DUE PAST DUE PAST DUE PAST DUE Total
Invoice # Customer# Name Due Date DUE 0-30 31-60 61-90 91-120 121-180 181+
----------- ------------- ---------------- ----------- -------------- -------------- -------------- -------------- -------------- -------------- ------------- -------------
GL #:10-0310-01700-31030-POWWS-00000
45424 60109128 Nat Re 08-MAR-18 0.00 0.00 0.00 0.00 1000.00 0.00 0.00 1000.00
-------------- -------------- -------------- -------------- -------------- -------------- -------------- --------------
Total for GL # 00-0310-01700-31030-POWWS-00000 0.00 0.00 0.00 0.00 1000.00 0.00 0.00 1000.00

GL #:10-1000-10000-17413-PEEE-00000
SF1-1160857333 45888888 Ar Adv 15-JUL-18 -3073.95 0.00 0.00 0.00 0.00 0.00 0.00 -3073.95
-------------- -------------- -------------- -------------- -------------- -------------- -------------- --------------

 

[kvisaria]

Hello

This is clarified version of my previous post.




I want to convert file from text to column
but only issue is some rows are not aligned as others. I have attached text file and sample excel file that it should look like after conversion.

Text file -
IEEE Credit Memo Register
30-JUN-18 18:17
IEEE Service Center Trx Date From: 27-MAY-2018 To: 30-JUN-2018 Page: 1


Invoice Credit Memo Invoice Orig. Invoice Amount
Credit Memo # Credited Customer Name Customer # Date Date Code Amount Credited
-------------- ----------------- ---------------------------- ------------ ----------- ----------- ----- -------------- --------------
Reason: CANCELLATION

Product #: ----- GL Account #: XX-XXXX-00000-12345-RENEW-00000

203790 X-XXXXXXXXXXX Sam AAAAAAAAAAA XXXXXXXX 30-MAY-18 21-MAY-18 USD 45.99 0.00
203791 X-XXXXXXXXXXX John ddddddd XXXXXXXX 31-MAY-18 30-MAY-18 USD 152.50 0.00
-----------------
Total Amount Credited By Product # / GL Account # 0.00


30-JUN-18 18:17
IEEE Service Center Trx Date From: 27-MAY-2018 To: 30-JUN-2018 Page: 2


Invoice Credit Memo Invoice Orig. Invoice Amount
Credit Memo # Credited Customer Name Customer # Date Date Code Amount Credited
-------------- ----------------- ---------------------------- ------------ ----------- ----------- ----- -------------- --------------


Product #: ----- GL Account #: XX-XXXX-10000-21110-XXXXX-00000

203791 X-XXXXXXXXXXX John EEEEE XXXXXXXX 31-MAY-18 30-MAY-18 USD 152.50 0.00
203791 X-XXXXXXXXXXX John EEEEE XXXXXXXX 31-MAY-18 30-MAY-18 USD 152.50 0.00

-----------------
Total Amount Credited By Product # / GL Account # 36.30




Sample excel output that I need -

GL ACCOUNT NUMBER CREDIT MEMO CM NUMBER INVOICE CUSTOMER NAME CUSTOMER NO CM DATE INVOICE DATE CODE ORIG INV CREDITED
REASON AMOUNT AMOUNT

XX-XXXX-00000-12345-RENEW-00000 CANCELLATION 203790 X-XXXXXXXXXXX Sam AAAAAAAAAAA XXXXXXXX 30-May-18 21-May-18 USD 45.99 0.00
XX-XXXX-00000-12345-RENEW-00000 CANCELLATION 203791 X-XXXXXXXXXXX John ddddddd XXXXXXXX 31-May-18 30-May-18 USD 152.50 0.00
XX-XXXX-10000-21110-XXXXX-00000 CANCELLATION 203791 X-XXXXXXXXXXX John EEEEE XXXXXXXX 31-May-18 30-May-18 USD 152.50 0.00
XX-XXXX-10000-21110-XXXXX-00000 CANCELLATION 203791 X-XXXXXXXXXXX John EEEEE XXXXXXXX 31-May-18 30-May-18 USD 152.50 0.00