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

 

[jtakw]

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

Your explanation is contradictory and confusing.
Please provide sample data/text string With expected results, in the way you have the data layout in your sheet(s).

 

[wkirkcrawford]

Your explanation is contradictory and confusing.
Please provide sample data/text string With expected results, in the way you have the data layout in your sheet(s).

Jtakw,

The string of text is, as of now, LANTUS 24 Units in the Morning, 18 Units + Humlog at Dinner Time.

The names will change and the number isn't always 2 digits.

All I want for it to return is either 24 or 18.

The results will be just elsewhere throughout my headers.

I want to change the numbers in just one location and it would appear everywhere I have it.

I hope this explains everything. Wait, I'm thinking I could cell outside of my sheet. I'm thinking about that. I would have to remember where that location is. I'm thinking.

W. Kirk Crawford
Tularosa, New Mexico

 

[kweaver]

If you used XL2BB with examples, we would be able to try to find a solution.

 

[wkirkcrawford]

Kweaver,

I have no idea what XL2BB is.

W. Kirk Crawford
Tularosa, New Mexico

 

[kweaver]

see: XL2BB

 

[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,

OK, I got the first part to work, but with this change. =IFERROR(RIGHT(SUBSTITUTE(TRIM(LEFT(SUBSTITUTE($AK1,"Units",REPT(" ",255),COLUMNS($av104)),255))," ",REPT(" ",255)),99)+0,"")
Which returns 24, which is just what I wanted.

But I was unable to extract the 18 to a different cell. I don't need COLUMNS. Also, I do not understand the ',255' part.

Maybe I'm asking for too much.

I plan to use that results, 24 and 18 in many different locations.

What if I use this string, 24 Morning / 18 Din.+ Humlog ? This might be easier.

I have learned over the years to have a given in one place and then reference that location to get that given data over and over again. I hope that made my thoughts clear.

Thanks for all of your help. I'm amazed.

W. Kirk Crawford
Tularosa, New Mexico

 

[jtakw]

Ok, I think (more like guess, since you still haven't given details on your setup and goal) I know what you're trying to do now.
First of all, don't get confused about the COLUMNS function in the formula, it really doesn't have anything to do with Columns, it's a way for the formula to Count which number within the string to extract,
(e.g. 1,2,3 - first, second, 3rd, etc...)

Now I'm assuming you'll be putting the formula(s) in various cell locations with no particular relation to the Cell with the original Text string.
So, use below updated formula, REPLACE the Cell reference to point to your original text string Cell (e.g. $AK$1, make sure you use Absolute referencing, Note the $ symbols)
To extract the 1st number, use 1 in the formula, to extract the 2nd number, change the 1 to 2, for 3rd number change the 1 to 3, etc.

This is based on the text string in OP:

Book3.xlsx
	A	B	C	D
9	LANTUS 24 Units in the Morning, 18 Units + HUMLOG at Dinner Time.	24
10				18
Sheet722

Cell Formulas
Range	Formula
B9	B9	=IFERROR(RIGHT(SUBSTITUTE(TRIM(LEFT(SUBSTITUTE($A$9,"Units",REPT(" ",255),1),255))," ",REPT(" ",255)),99)+0,"")
D10	D10	=IFERROR(RIGHT(SUBSTITUTE(TRIM(LEFT(SUBSTITUTE($A$9,"Units",REPT(" ",255),2),255))," ",REPT(" ",255)),99)+0,"")

 

[jtakw]

Too late to edit.

Also want to point out, once you have the formula(s) in place at your desired cell(s), you can then, if you choose, refer to Those cells for the numbers:

Book3.xlsx
	A	B	C	D
9	LANTUS 24 Units in the Morning, 18 Units + HUMLOG at Dinner Time.	24
10				18
11
12
13			24
14		18
Sheet722

Cell Formulas
Range	Formula
B9	B9	=IFERROR(RIGHT(SUBSTITUTE(TRIM(LEFT(SUBSTITUTE($A$9,"Units",REPT(" ",255),1),255))," ",REPT(" ",255)),99)+0,"")
D10	D10	=IFERROR(RIGHT(SUBSTITUTE(TRIM(LEFT(SUBSTITUTE($A$9,"Units",REPT(" ",255),2),255))," ",REPT(" ",255)),99)+0,"")
C13	C13	=B9
B14	B14	=D10

 

[wkirkcrawford]

Jtakw,

That's what I'm looking for.
Thank you so very much.

W. Kirk Crawford
Tularosa, New Mexico

 

[jtakw]

You're very welcome, I'm glad we got it worked out for you, thanks for the feedback.