Advanced Column Splitting - multiple characters and lengths

Jonsmith

New Member
Joined
Jan 31, 2017
Messages
1
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

MrExcel MVP
Joined
Dec 18, 2014
Messages
1,189
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.
 

Forum statistics

Threads
1,084,993
Messages
5,381,062
Members
401,713
Latest member
BraydenMSB

Some videos you may like

This Week's Hot Topics

Top