Convert Hours minutes and Kilometer text values to numeric

Good Morning,

I was wondering if someone can help me with this data output.
Each day a human will manually export the data and paste it into a spreadsheet then add the date to column A. Then the next day they will paste the newly exported records at the bottom of the list. This will continue day after day and year over year.
I was hoping someone would have a macro solution they could run after pasting the data each day that loops through the records and does the conversions.

I have a little bit of VBA understanding. If the code could be commented so I can maintain it if necessary, I would appreciate that.

I originally started doing this with formulas but, after some discussion with the end users, by the end of the year this could have over 100,000 rows, pivot table reports and formulas. They want to use this for a year over year reporting so after a few years we could be looking at 100K rows every year.
I figured that would make this workbook very slow. I realize that even after a couple years this might not be optimal. Excel is their only option at present.

Basically what I am looking for is a way to convert the values in columns D, E, F, H from hours and minutes text value to the rounded off numeric value of total minutes.
The values from the export look like this along with what I would like it converted to:
( i did notice that values with minutes only are 9 characters long and values with Hours and minutes are 16. It never varies 9 or 16)

"01 hrs 23.00 min" convert to 83
"41.24 min" convert to 41
"23.50 min" convert to 24
"01 hrs 28.21 min" convert to 88

Likewise column "i" will need to be converted to just the rounded kilometer number.

"157.56 km" convert to 158
"0 km" convert to 0
"202.67 km" convert to 203
"68.67 km" convert to 69

Please let me know if you require further clarification.
I really appreciate everyone's time and input on this matter.

You could do this with formulas if you put your data into a table. Failing that, here's a VBA solution that would ignore previously converted cells:

Please Login or Register  to view this content.

WBD

Originally Posted by WideBoyDixon

You could do this with formulas if you put your data into a table. Failing that, here's a VBA solution that would ignore previously converted cells:
WBD

Thank you very much for this solution. I tested it on 10,00 rows and it worked great, I tried it on 100K rows, took a few minutes, tried it 3 times on 600K rows and I think I broke excel, LOL.
As far as I am concerned this is perfect.

EDIT: Just to note: I was converting the entire ranges. When I just convert the newly added range, just a second or 2 every time. Even with 600K rows, just a few seconds to convert the newly added records.

Could I ask you to put some comments in this so I can figure out what it is doing, in case I need to tweak it at all.

Thank you very much.

Rep'd

Gray

None of that sounds like it couldn't be automated.

If the extraneous data data is always in the same format then it's identifiable through code regardless of where it sits in the data, which means it can be deleted prior to import or simply ignored when copying and pasting with code.

If they may not extract the report daily then you could have a simple "processing" folder where each of the multiple reports are saved temporarily. The code could loop through each file in that folder, check it's actually one that needs to be imported then import it before parking it on a "processed" folder. There are other ways this can be dealt with too, such as appending a flag to the file name to show it's already been preocessed and therefore ignored on the next run.

The date can obviously be added through code.

Automation of tasks like this (or human error proofing as I like to call it) is a fair chunk of my day job so I've found many ways over the years to turn long winded manual processes into "click a button" exercises.

BSB

OK. On reflection you can only examine the pasted rows by responding to Worksheet_Change()

Please Login or Register  to view this content.

WBD