Forum Discussion
How to Extract a Number from a Text String?
For example of a text string of train arrivals at a station "Ar. 35 min late" or "Dp. 4 hr, 9 min late" For the rare situation when the train is more that 59 minutes late (in the second exampl...
Let's say you have such text strings in D2 and down.
In another column in row 2:
=LET(parts, TEXTSPLIT(D2, " "), n, COUNTA(parts), mins, INDEX(parts, n-2), hrs, IF(n>4, INDEX(parts, n-4), 0), 60*hrs+mins)
to return the number of minutes, or
=LET(parts, TEXTSPLIT(D2, " "), n, COUNTA(parts), mins, INDEX(parts, n-2), hrs, IF(n>4, INDEX(parts, n-4), 0), (60*hrs+mins)/1440)
to return a time - in this case, format the cell with the formula as time.
The formula can be filled down.
