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

 

[kweaver]

(a) are the numbers always 2-digits? (b) please update your profile to show what version of Excel you're running.

 

[kweaver]

BTW: do you want the numbers in 1 cell? What delimiter? Are there always only 2 numbers?

 

[wkirkcrawford]

Kweaver,

No, the number isn't always 2 digits.

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

W. Kirk Crawford
Tularosa, New Mexico

 

[kweaver]

Kweaver,

No, the number isn't always 2 digits.

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

W. Kirk Crawford
Tularosa, New Mexico

What result do you want in this example? It would help if you show a few examples with the desired results.

 

[jtakw]

Hi,

Assuming you want the numbers extracted to separate columns, and "Units" always immediately following the numbers, drag formula as far across columns as necessary.

Book3.xlsx
	A	B	C	D	E
1	LANTUS 24 Units in the Morning, 18 Units + HUMLOG at Dinner Time.	24	18
2	LANTUS 224 Units in the Morning, 18888 Units + HUMLOG at Dinner Time, 2 Units COOKIES before bed.	224	18888	2
Sheet722

Cell Formulas
Range	Formula
B1:E2	B1	=IFERROR(RIGHT(SUBSTITUTE(TRIM(LEFT(SUBSTITUTE($A1,"Units",REPT(" ",255),COLUMNS($B1:B1)),255))," ",REPT(" ",255)),99)+0,"")

 

[Joe4]

In these kind of problems, it is usually best to show multiple examples, to try to capture all of the distinct structures and possibilities.
We need to see all the different ways this data might appear, and what your expected results look like.

 

[wkirkcrawford]

Hi,

Assuming you want the numbers extracted to separate columns, and "Units" always immediately following the numbers, drag formula as far across columns as necessary.

Book3.xlsx
	A	B	C	D	E
1	LANTUS 24 Units in the Morning, 18 Units + HUMLOG at Dinner Time.	24	18
2	LANTUS 224 Units in the Morning, 18888 Units + HUMLOG at Dinner Time, 2 Units COOKIES before bed.	224	18888	2
Sheet722

Cell Formulas
Range	Formula
B1:E2	B1	=IFERROR(RIGHT(SUBSTITUTE(TRIM(LEFT(SUBSTITUTE($A1,"Units",REPT(" ",255),COLUMNS($B1:B1)),255))," ",REPT(" ",255)),99)+0,"")

Jtakw,

I couldn't get that to work.

Thanks.

W. Kirk Crawford
Tularosa, New Mexico

 

[jtakw]

As you can see, worked for me in my sample in Post #6.
Please explain How it didn't work for you....?
What's the Text string you're using, what cell it's in, and where are you extracting the numbers to...?

 

[wkirkcrawford]

As you can see, worked for me in my sample in Post #6.
Please explain How it didn't work for you....?
What's the Text string you're using, what cell it's in, and where are you extracting the numbers to...?

Jtakw,

You are using two other cells. I can't use that. Oh yes, I know, I could use it in some hidden cells, but I don't want that.

You also assumed I was going to put that info into other columns.

I just want to extract each number in different cells.

W. Kirk Crawford
Tularosa, New Mexico