Google Sheet Split from 24:00 forcing to 24:00:00.000

Question

I'm trying to work this out, can't find a solution.

The string, after REGEXEXTRACT is 20:00 24:00 25:00

I've been banging my head for hours, trying ways to convert, after the split, to get this result:

20:00 24:00 25:00

But it turns out that Google Sheets always forces:

20:00 24:00:00 25:00:00

Tried converting to text, using arrayformula, formatting cell as custom date, etc. Nothing has worked, so far.

Here a sheet example, already with the SPLIT result and the desired result.

Any clues?

Thanks! ;)

Erik Tyler · Answer

I edited D14:F14 to get the result you want. You just needed to set the format for the output range to durations not military time:

Format > Number > Custom number format > [h]:mm

player0 · Answer

you can force it:

=ARRAYFORMULA(TEXT(SPLIT(D10; " "); {"[h]:mm"\ "[h]:mm"\ "[h]:mm"}))