alphanumerics in duration in sheets - how to convert to a number of minutes

Question

Working in google sheets, I am getting input from another program in the form of "Xh Ym Zs" to track time. I'm trying to write a formula that will just yield a number of minutes, so "2h 22m 5s" would give 142.

I'm trying all sorts of things with find, search, regexmatch, but I'm running into 2 problems -- one is that there's no set number of characters in each category -- 1h 5m 40s instead of 01h 05m 40s. the other is that the input I'm getting just leaves out a category if it's a zero -- if a student was working for just 35 minutes, the program gives me '35m', not 0h 35m 0s. Similarly, a student who worked for 4 hours and 25 seconds yields '4h 25s'.

Is it even possible to do this with one formula?

Answer 1

Use regexextract(), like this:

=let( 
  get_, lambda(s, ifna(regexextract(A2, "(\d+)" & s), 0)), 
  duration, value(join(":", get_("h"), get_("m"), get_("s"))), 
  iferror(round(24 * 60 * duration)) 
)

See let(), lambda(), ifna(), regexextract(), value(), join(), iferror(), and round().

For some background, see Working with date and time values in Google Sheets.