Split Cell Into Two And Move To Another Row And Columns

I have a report that I need to reformat where part of the information is moved from rows to columns.

The report is broken up into "sections" as follows: Each “section” is a series of multiple rows and is broken down as follows:

ROW 1: Contains data (in a single cell) about a Sales rep, which includes (1) rep number & (2) rep name

NEXT ROW(s): Contains data information about an invoice(s), which includes date, invoice number, client name, trans ID, etc. The invoice data can be one row up to as many as 500 rows
LAST ROW: Contains the Rep Subtotal

I need to spit out a report that contains the invoice data only (the middle part of the “section”). I don't want "ROW 1" or "LAST ROW" of each section in the output. For each invoice row, I need to include the rep number and the rep name for each invoice. As noted, the rep number and name is always listed in the row preceding the invoice data. The format is always a 6-digit code followed by the name. So I need to split the data into two pieces.