Excel data in different layout
I have a Power Query retrieving the customer data in one sheet. The format of the data is in one line for a customer including the File info (Expected output format: line1), Customer payment info (Line 2), each payment records (line3-4) and footer (line 5). Now I need to divide one line data into 5 lines as Expected output format below. The Power Query retrieving data sheet contains multiple customers. Is that possible to do so in Excel? Could you help me out?
Original data:
F 1 231.00 000000001 T P CHK 1012345678 231.00 388 Greenwich Street New York NY 10013 US R INV123 120.12 202000401 ACCT456 ID789 PO234 R INV124 110.88 202000401 ACCT456 ID790 PO235 N Payment for Widgets shipped Nome AK
Expected output format:
F 1 231.00 000000001 T
P CHK 1012345678 231.00 388 Greenwich Street New York NY 10013 US
R INV123 120.12 202000401 ACCT456 ID789 PO234
R INV124 110.88 202000401 ACCT456 ID790 PO235
N Payment for Widgets shipped Nome AK
Thanks in advance!
Jane
|