Hi Guys,
The goal is to have one clean row of Time entries.
After many attempts at trying to solve this problem I am now here seeing if anyone else has some bright ideas.
Very simple problem that needs a fresh solution if there exists. I have CSV with thousands of faulty time entries that are missing a 0 or two messing up how they will be viewed. All is not lost though as there is a pattern to the missing zeros- that being any Zero to the left of the right digit in HH/MM/SS format has been erased.
better explaining - the fault happens when time entry should say - 19:32:06
but in the CSV it will be reproduced as 19:32:6
Or 00:06:02
but in the CSV it will be reproduced as 0:6:2
This problem is further exacerbated by the fact that the faulty entries are scattered across the sheet.
My only method successful method is splitting the entries into different cells but I would still have to manually move each faulty number into the correct column and then fill column with the missing zero one by one.
I then thought my main problem in this solution i have created is not being able to highlight the correct cells that need simply shifting to the right. If I could do this easily then shifting them at the same time... would save time, then i could flash fill the appropriate cells with zeros. but this has proved unsuccessful.
I did try kutools reverse selection feature, but that did not work for what i'm trying to do.
Can someone point me in the right direction or provide help to solve this conundrum, failing a new solution I will have to manually, carefully sort the CSV and that might be a few days work at best. I have provided a file for anyone to mess about with if you need it-
Many thanks
drive.google.com
The goal is to have one clean row of Time entries.
After many attempts at trying to solve this problem I am now here seeing if anyone else has some bright ideas.
Very simple problem that needs a fresh solution if there exists. I have CSV with thousands of faulty time entries that are missing a 0 or two messing up how they will be viewed. All is not lost though as there is a pattern to the missing zeros- that being any Zero to the left of the right digit in HH/MM/SS format has been erased.
better explaining - the fault happens when time entry should say - 19:32:06
but in the CSV it will be reproduced as 19:32:6
Or 00:06:02
but in the CSV it will be reproduced as 0:6:2
This problem is further exacerbated by the fact that the faulty entries are scattered across the sheet.
My only method successful method is splitting the entries into different cells but I would still have to manually move each faulty number into the correct column and then fill column with the missing zero one by one.
I then thought my main problem in this solution i have created is not being able to highlight the correct cells that need simply shifting to the right. If I could do this easily then shifting them at the same time... would save time, then i could flash fill the appropriate cells with zeros. but this has proved unsuccessful.
I did try kutools reverse selection feature, but that did not work for what i'm trying to do.
Can someone point me in the right direction or provide help to solve this conundrum, failing a new solution I will have to manually, carefully sort the CSV and that might be a few days work at best. I have provided a file for anyone to mess about with if you need it-
Many thanks
timesortout.xlsx
drive.google.com
