Returning number from Description

[vvinod]

is there any formula to find only receipt quantity from below description?

Transaction_id:1880208591, Org Code: , Receipt_num:154513, Item:320829000042, Rect Qty:125 MT , , Item GL Class (Org Level): RMS , GL Product Line (Master level): 320.829.999 PO Freight Term: CFR

I need to get the values as 125 in this case

 

[felixstraube]

how about:

=LET(t,RIGHT(A1,LEN(A1)-FIND("Rect Qty",A1)-8), LEFT(t,FIND(" ",t)))

 

[vvinod]

Not getting

 

[vvinod]

Sorry mine is office 2019 , not understanding what is this LET and t, i put below one my column is S2.

=LET(t,RIGHT(S2,LEN(S2)-FIND("Rect Qty",S2)-8), LEFT(t,FIND(" ",t)))

Transaction_id:1880208591, Org Code:180, Receipt_num:154513, Item:320829000042, Rect Qty:125 MT , , Item GL Class (Org Level): RMS , GL Product Line (Master level): 320.829.999 PO Freight Term: CFR

 

[felixstraube]

Ohhh I see... in your profile you display that you have 2021.
Let me modify the formula for 2019.

 

[Cubist]

One option:

=MID(S2, FIND("Rect Qty:", S2) + 9, FIND(" MT", S2) - FIND("Rect Qty:", S2) - 9)

 

[felixstraube]

Here the formula for 2019:

=LEFT(RIGHT(A1,LEN(A1)-FIND("Rect Qty",A1)-8),FIND(" ",RIGHT(A1,LEN(A1)-FIND("Rect Qty",A1)-8)))

Let me know it if works for you.

 

[felixstraube]

If you share more data we could see if the formulas works for all your cases.

 

[vvinod]

Here the formula for 2019:

=LEFT(RIGHT(A1,LEN(A1)-FIND("Rect Qty",A1)-8),FIND(" ",RIGHT(A1,LEN(A1)-FIND("Rect Qty",A1)-8)))

Let me know it if works for you.

Thank you so much, you made me my work easy........

 

[vvinod]

If you share more data we could see if the formulas works for all your cases.

Thank you this also worked