In a Google sheet, I have a list of rows with a length attached to each:

Example 1 - 1h 30m

Using a formula, I need to convert "1h 30m" to a numerical value. The longest duration I have in the sheet are 4 digit durations (ie 1000h 30m)

Does anyone know how to best do this?

CodePudding user response：

Assuming the text string 1h 30m is in cell B2, use a regexreplace() formula in another cell, like this:

=to_date( value( regexreplace(B2, "(\d )h (\d )m", "$1:$2") ) )

Format the formula cell as Format > Number > Duration or a custom time format of your choosing.

See this answer for an explanation of how date and time values work in spreadsheets.

To learn the exact regular expression syntax used by Google Sheets, see RE2.