How to Extract a Number from a Text String?

[trainvisitors]

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 example, I can manually make the conversion.  Most are less than 59 minutes late.

[#2]

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.

[#3]

Can you show your expected result?

[#4]

This is a 365 solution that will find the times for an entire array of entries similar to what you've provided.

=LET(
    GetNum, LAMBDA(unit,
        TEXTAFTER(TEXTBEFORE(train_times, " " & unit, , , , 0), " ", -1, , , 0)
    ),
    minutes, 1 * GetNum("min"),
    hrs, 60 * GetNum("hr"),
    hrs + minutes
)

Train_times is a named range containing the text entries.

The numbers being returned are all in minutes.