How to extract number is a string of text?

[wkirkcrawford]

Greetings,

This is my string; LANTUS 24 Units in the Morning, 18 Units + HUMLOG at Dinner Time.

I need to extract the numbers, 24 and 18.

I've seen all kinds of answers, including VBA, which isn't for me.

W. Kirk Crawford
Tularosa, New Mexico

 

[Peter_SSs]

Ver.#, Microsoft 365 MSO (16.0.13530.20054) 32-bit

Please put this information in your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

If you are interested, here are a couple of slightly shorter options that you might like to also try.
Note that these formulas (and those of jtakw) rely on the word "Units" in your text being exactly like that and not "UNITS", "units" etc. For my formulas, if the case of "Units" could vary, change each FIND function to SEARCH.

21 02 03.xlsm
	A	B	C	D
9	LANTUS 24 Units in the Morning, 18 Units + HUMLOG at Dinner Time.	24
10				18
Extract Nums

Cell Formulas
Range	Formula
B9	B9	=IFERROR(AGGREGATE(14,6,RIGHT(LEFT(A9,FIND("Units",A9)-1),SEQUENCE(20))+0,1),"")
D10	D10	=IFERROR(AGGREGATE(14,6,RIGHT(LEFT(A9,FIND("Units",A9,FIND("Units",A9)+1)-1),SEQUENCE(20))+0,1),"")