522 Companies and 8872 People to be transfered from one spreadsheet to 2 spreadsheets

Description of excel work: Hi,

I currently have one spreadsheet, I have attached a sample (raw data). This includes details of 522 companies across the UK with details of 2833 Directors and 6039 Management within the companies.

I need this information to be split across two seperate spreadsheets : one company and one people (both attached)

Need the following transfered across to each spreadsheet from the one -

Company Template :

Company Name
Address - Needs to be split on raw data to 6 columns max (as per company template)
Phone Number
Website
Company size
Turnover


People Template :

Company Name
Company Town - pulled from above when address has been split
Company Postcode - pulled from above when address has been split
Company County - pulled from above when address has been split
Company Country - pulled from above when address has been split

First Name and Surname (split) of all directors and contacts within each company (Column L&O on raw)
Job Title (Column M&P on raw)


Thanks
Extra notes: