Selecting text to the right of text in a string

[Lukevancleave]

Hi, I'm trying to use a column with a string created from an OCR scan of an image to populate other columns in a form.  So far my string looks something like this 

Order# 0068348 BOL# XXXXX o From: DOWAGIAC, MI 49047  To:  IRON RIDGE, WI 53035 call Ship date: April 28, 2022, 0001 - 2359 Delivery date: April 29, 2022, 0001 - 2359 Carrier Instructions: , Loaded miles: 239, Empty miles: 167, leave one copy of the BOL DO NOT BOBTAIL INTO THIS LOCATION NO REEFER TRAILERS AT THIS LOCATION MUST BRING A DRY VAN

And I am trying to extract something like the "239" loaded miles.  So I'm trying to make a virtual column that says find "Loaded miles: " and then return the number after it.  Are there any text expressions I can use to do that?  Thanks

[Rafael_ANEIC-PY · Accepted solution]

Hello @Lukevancleave , try with this expression:

MID([stringColumn],
FIND(" Loaded miles: ",[stringColumn])+LEN(" Loaded miles: "),
FIND(", Empty miles:",[stringColumn])-FIND(" Loaded miles: ",[stringColumn])-LEN(" Loaded miles: ")
)

[Suvrutt_Gurjar · Accepted solution]

Nice one @Rafael_ANEIC-PY 

@Lukevancleave : You could also try the following expression  for loaded miles if you are not likely to have mentions (ie @) in your OCR string and your OCR pattern is standard. Please test well.

SUBSTITUTE(EXTRACTMENTIONS(SUBSTITUTE([OCRString],"Loaded miles: ","@")),"@","")

And this one for empty miles:

SUBSTITUTE(EXTRACTMENTIONS(SUBSTITUTE([OCRString],"Empty miles: ","@")),"@","")

https://help.appsheet.com/en/articles/3497820-extractmentions