How to convert min and second from text format to numbers

[HelenaX]

I have a table column that has the time in text format. For instance, below are a few examples:

RunTime

35 s

6 min 6 s

6 mim

I need to sum the total RunTime, however the text format is preventing me from doing so. I tried to change the data type to Time but it didn't work. I also tried to use extract function but failed too becuase the format is not consistent (i.e. It is not always in "xx  min xx s" format. When it is within a minute, it removes the 0 min)

Anyone has suggestions on how to convert this text format to numbers?

[mahoneypat · Accepted solution]

You can add a custom column with this formula (put this is the pop-up box when you hit Add Custom Column).

let
thislist = Text.Split([RunTime], " ")
in
if List.Count(thislist) = 4
then Number.FromText(thislist{0}) * 60 + Number.FromText(thislist{2})
else
if List.Contains(thislist, "s")
then Number.FromText(thislist{0})
else Number.FromText(thislist{0}) * 60

Pat

[HelenaX]

@mahoneypat  Just out of curiosity, what does the 0 and 1 in below formular mean?

===

if List.Count(thislist) = 4
then Number.FromText(thislist{0}) * 60 + Number.FromText(thislist{2})

===

I am guessing it has something to do with the position of the number in the text. 

For instance, in the case of text

    35 min 4 s

Does the 0 refer to the 35 and the 2 refer to the 4?

If so, why 0 position and 2 position? Why not 1 position?

[mahoneypat]

The Text.Split splits your text into a list with either 2 or 4 elements.  Since Power Query starts counting at 0, the {0} and {2} get the first and 3rd elements of the list.

Pat

[HelenaX]

@mahoneypat  Thank you for the explanation! Really appreciate your help!

[HelenaX]

@mahoneypat  Thank you for your help!! I got it!!