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

excel
Formulas and Functions

3 Replies

  • 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.

     

     

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jan 03, 2024

    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.

Resources