VBA Convert Time from weird format

I am working on a subroutine that will help me transform call records into something useful for analysis.

Column B contains values that refer to the duration of the phone call, however the formatting is quite strange. Here are some values:

47s 472ms
53s 844ms
55s 164ms
2mn 17s
1mn 5s
1mn 25s

As you can tell, if the call is less than 1 minute, the format is seconds then milliseconds. If the call is greater than 1 minute, the format is minutes then seconds.

I need to convert those values to seconds (so ignore the milliseconds when they appear although ideally I would like to round up / down for milliseconds). I would also like the new value to be in seconds so if the time is 1mn 5s, the new value should be 65. If the value is 53s 844ms, the new value should be 54.

Would you mind helping me write the VBA code? Please let me know if you need any more clarification.

Thanks!

Thanks, TMS. I think that is almost right unless I am doing something wrong. When I ran "test" for value 30s 528ms the msgbox returned 12:31:00AM, which is close. It appears the seconds are being assigned the minutes place.

I think I got it using a different approach:

Please Login or Register  to view this content.

Thanks for the fix, TMShucks. And thank you Leith Ross. I'm learning a lot here.