Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

How to Extract a Number from a Text String?

Copper Contributor

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.

3 Replies

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.

Can you show your expected result?

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.

Patrick2788_0-1704322787191.png

 

The numbers being returned are all in minutes.