Extracting numbers from text?

**shiftyspina** · 01-15-2014, 08:33 AM

Hi,

Is there a way to extract the number from a string of text using a formula rather than a macro?

I have tried Left, Right, Len and Mid, and other functions i usually use, but because the numbers appear if different locations of the text i cant get it to work throughout using 1 formula.....

What i am trying to do is extract the number only from this list

BLUSH PEAR LOOSE 12KG
ROCHA LSE 13KG
PEAR BASIC X24
LOOSE GREEN PEAR 13KG
BLUSH PEAR LOOSE 13KG
BLUSH PEAR POLY X14
BLUSH PEAR POLY X14
TTD PEAR X14
BLUSH PEAR LOOSE 13KG
CONFERENCE LOOSE 12KG
CONFERENCE POLY X14
DESSERT PEAR POLY X16
ORGANIC GENERIC PEAR X7

So i would want in another cell for it to say

12
13
24
13
13
14
14
14
13
12
14
16
7

Is there a simple way to do this or does it involve having to build formulas into other columns to break down the text to then find the number?

Thanks

Dan

**Pepe Le Mokko** · 01-15-2014, 08:38 AM

Have a look at https://office.microsoft.com/en-us/e...001154901.aspx

**Sam Capricci** · 01-15-2014, 08:45 AM

You could try something like this...
=LOOKUP(9E+99+307,--MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&1234567890)),ROW(INDIRECT("1:"&LEN(A1)))))
tried it w/yours and it appears to work.

**shiftyspina** · 01-15-2014, 08:56 AM

Hi thank you both, that link was quite useful Pepe thank you

Sambo i cant seem to get that one to work, what is the (9E+99+307.... i understand what the rest is doing, should be doing, but i dont know what that first bit represents? is it a formatting lookup? i would of thought my cell (example a1) would go after lookup(.......?

**AlKey** · 01-15-2014, 09:00 AM

Try this

=--MID(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),2)

	A	B
2	BLUSH PEAR LOOSE 12KG	12
3	ROCHA LSE 13KG	13
4	PEAR BASIC X24	24
5	LOOSE GREEN PEAR 13KG	13
6	BLUSH PEAR LOOSE 13KG	13
7	BLUSH PEAR POLY X14	14
8	BLUSH PEAR POLY X14	14
9	TTD PEAR X14	14
10	BLUSH PEAR LOOSE 13KG	13
11	CONFERENCE LOOSE 12KG	12
12	CONFERENCE POLY X14	14
13	DESSERT PEAR POLY X16	16
14	ORGANIC GENERIC PEAR X7	7

**Sam Capricci** · 01-15-2014, 09:03 AM

I would be BS'ing you if I tried to explain that part, I found the formula once and have used it ever since.

What I'm not sure of is why it isn't working for you. I copied your samples into a spreadsheet then applied the formula and it worked fine.
And I see that AlKey's variant on the formula also works.
What isn't working w/mine?

**AlKey** · 01-15-2014, 09:15 AM

@Sambo kid

Don't worry. Your formula is also working. It just has some unneeded elements.

BTW, thank you for the Rep

**shiftyspina** · 01-15-2014, 09:16 AM

I have tried both and neither work, on my sheet, it may be that i have simplified the data im trying to extract the number from, which is in a data table, but i did try Pepe's link which i found this formula from and this works perfect

=1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$9),1)),0),COUNT(1*MID(A1,ROW($1:$9),1)))

Im going to keep trying with both your formulas as if it works for you two, it surely must work for me,

Thank you all

**Sam Capricci** · 01-15-2014, 09:28 AM

You're welcome and thx for the bump to the rep.

BTW, I can't get yours to work on my sample for where I pasted it (around row 585 and after I altered it to grab the same cells). Will have to read that link again to see what it is saying.