Forum Discussion

trainvisitors's avatar
trainvisitors
Copper Contributor
Jan 03, 2024

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...
excel
Formulas and Functions
Patrick2788's avatar
Patrick2788
Silver Contributor
Jan 03, 2024

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.

 

 

Resources