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

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

3 Replies

# Re: How to Extract a Number from a Text String?

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.

# Re: How to Extract a Number from a Text String?

Can you show your expected result?

# Re: How to Extract a Number from a Text String?

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.