Advanced Column Splitting - multiple characters and lengths

[Jonsmith]

Hello there!

I have a csv file that packs data in one column like this:

{{Name of Person,,,,,0.5,,,,11/19/2017 9:35:00 AM},{Vehicle used,,,,,0.5,,,,11/19/2015 9:35:00 AM},{Name of Person,,,,,0.5,,,,11/19/2017 9:35:00 AM},{,,,,,,,,,11/19/2017 9:35:52 AM}}

<tbody>
</tbody>

I am just after the name and the ".5" value (which varies as it is time spent on a job). The data can look like this or it can be short and only contain something like this:

{{Name of Person,,,,,0.5,,,,11/19/2017 9:35:00 AM} Is it possible to split this uni-formally in Power Query? If not I have another data set I could join; however the formatting of that presents problems. The data has three columns: JOB ID EMPLOYEE NAME HOURS however there is only one row with a job ID and multiple resources tied to it, like this: JOB ID EMPLOYEE NAME HOURS 1234 employee 1 .5 employee 2 .5 2345 employee2 1 employee1 1 Anyway, I appreciate any feedback!

<tbody>
</tbody>

 

[Matt Allington]

You can split on every instance of comma. As long as the hours always ends up in the same column, just delete the columns you don't need.