I have a spreadsheet for a logbook I need to maintain for work. Unfortunately, the data that work gives me is from a website that I have to copy and paste into a excel spreadsheet and then manually format to get it into the right format.
When I copy and paste the data from the website (see image number 1) and then paste into Excel, it all gets pasted into one column, without commas (see image number 2)
I would like that data to be extracted and placed into separate cells on a different tab of the excel file (see image number 4).
As an example, formatting and extracting the data from the highlighted row on image number 3 -
I would like the data from row #3
Aug 19 2018 773 NICHOLAS CLARK VAISILIVA MCLEOD MEL-LAX 14.25
To then be placed in columns in a different tab and formatted this way:
19/08/2018 773 [login to view URL] MEL-LAX 14.25
As you can see:
1) Column 1 - The date is needed in DD/MM/YYYY format
2) Column 2 - 773 goes in here
3) Column 3 - First name NICHOLAS CLARK goes in here, but truncating the first name so that the name is formatted as [login to view URL]
4) Column 4- MEL-LAX or LAX-BNE etc goes in here
5) Column 5 - The number, in this case 14.25 goes in here
(Noting that the second name VAISILVIA MCLEOD isn't imported)
Any row from the unformatted data (see image number 1) that does't have data, and only shows a ADO, FLY, DDO, SBY etc doesn't get imported into the second tab. All the data that is formatted over to the second tab will be all underneath each other with no spare rows where the was no data,
For example, in image number 1, the data for Aug 19 2018, Aug 20 2018, Sep 02 2018, Sep 03 2018, Sep 09 2018 and Sep 11 2018 would be extracted over to the second tab such that there would be (in this example) 6 rows of data without any spare rows between the data where the DDO, ADO, SBY rows would have been.
I have uploaded a .txt file of an example of the raw data I have copied from the website. This is exactly how the data is presented on the website. Unfortunately, I cannot download as a .cvs or excel file, only the raw data, which makes this project somewhat harder
I AM WILLING TO PAY A FIXED PRICE OF $50 AUD FOR THIS PROJECT
40 pekerja bebas membida secara purata $34 untuk pekerjaan ini
I have extensive experience developing and validating VBA macros for the manipulation of data. I work in a healthcare environment, therefore attention to detail accuracy and precision are imperative.