1

I have a series of numbers that, for a reason out of my control I can not change, are expressed as plain text strings on google sheets.

The numbers are supposed to be minute second. mm:ss. For the life of me, I can not figure out how to add them together. Does anyone have any ideas?

The desired result would be something like:

01:50
06:10
60:00
"Random text"
""
05:30

Resulting time 73:30 OR 01:13:30 (I'm not picky about which one)

0

=ARRAYFORMULA(TEXT(SUM(IFERROR(TIME(0, 
 REGEXEXTRACT(A1:A, "(.*):"), 
 REGEXEXTRACT(A1:A, ":(.*)")))), "hh:mm:ss"))

0


=ARRAYFORMULA(TEXT(SUM(IFERROR(TIME(0, 
 REGEXEXTRACT(A1:A, "(.*):"), 
 REGEXEXTRACT(A1:A, ":(.*)")))), "[m]:ss"))

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.