Excel formulas and macro

We’re looking develop formulas and a macro to reformat a document for our regular data import process. We receive a report from a partner organization on a daily basis and regularly need to reformat and upload the ticket data into our system. These are the basic requirements to make the sheet ready for our import: • Partitioning the Name field provided into First, Last, First 2, Last 2 columns • Partitioning the Csz column into separate city, state and postal code columns • Removing foreign country zip codes • Adjusting zip codes that start with 0 (the file does not include the 0 if it’s the leading number) • Removing extraneous columns • Removing rows with missing seats/seat locations • Partitioning the Seat Location column into Section, Row, First Seat, Last Seat columns • Creating multiple rows in the worksheet if the Seat Location column value represented non-contiguous seats We have had particular difficulty with the last point, separating non-contiguous seats into separate lines. All the biographical data should be present with above listed formatting, and all data split into multiple lines with the price and total seat count reflecting only the contiguous set of seats from the order. We receive this file as a .csv and ultimately convert it to tab-delimited text file for import. I’ve included a document that contains a couple of examples of data as we receive it and reformatted for import, one standard example and one with non-contiguous seats. The first sheet of the document includes the file preparation instructions. More example sheets can be shared in the future.