How to convert alphanumeric time to seconds in Google Sheets

1
votes

I am having time in alphanumeric form in column in Google sheet. For example 1d 6h 30m, 30m 20s etc. I want to convert these values to seconds using formula in Google sheet. Couldn't find straight formula to do it. Any help would be really appreciated.

1
google-sheetsarray-formulas

1 Answers

3
votes

Give this a try (assuming source data in column A, starting in row 2)

=ArrayFormula(if(len(A2:A), mmult(iferror(regexextract(split(A2:A, " "), "\d+")*VLOOKUP(regexextract(split(A2:A, " "),"[dhms]"), {"d", 86400; "h", 3600; "m", 60; "s", 1}, 2, 0), 0), sequence(max(len(regexreplace(A2:A, "\d+|\s",))), 1, 1, 0)),))

Example