Google-sheets – Adding mm:ss times when expressed as TEXT

formulasgoogle sheetsgoogle-sheets-arrayformulagoogle-sheets-datestext formatting

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)

Best Answer

=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

Related Topic