A formula to pick no from a sentence

[ahamed]

Hi

I have list of names of products which include code, I'm looking for formula that can distinguish the text from the letters (in same order as it is entered)


Abc125mmXc = a formula to pic only 125
Cbf456wnXd = a formula to pic only 456
Ghe885trXe = a formula to pic only 885

Thank you

 

[Robert Mika]

=lookup(99^99,--(0&mid(a1,min(find({0,1,2,3,4,5,6,7,8,9},a1&1234567890)),row(indirect("1:"&len(a1)+1)))))

 

[p45cal]

…and see this thread.

 

[villy]

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

*Note: CTRL+SHIFT+(Enter) to be entered as an array formula..
From:MS Office2010 Help

 

[Peter_SSs]

You have been given a number of alternatives for extracting a number from within a string. However, if your data is as uniform as your 3 samples, then this simpler formula may be all that is required.

=MID(A1,4,3)+0

 

[ahamed]

Dear All

Thank you very much Robert Mika, p45cal, villy, Peter_SSs,for your quick reply.

I hope I got the answer,

 

[ahamed]

Hi every one

Sorry, I hope I have another issue. Previously I thought having one set of number will serve my purpose, I think I need entire code to get into separate cell;

let me put as follow ;

Empty cans (can size 211X 109) Crown = (need to pick only) 211X 109
Empty cans (can size 307 x 111) APC = 307 x 111
Empty cans ( can size 307 x 112) = 307 x 112
Empty cans ( can size 307 x 113) APC = 307 x 113

I would really appreciate if any one can help me on this, sorry for any trouble

 

[Yahya]

try this

Excel Workbook
	A	B
3	Empty cans (can size 211 X 109) Crown	211 X 109
4	Empty cans (can size 307 x 111) APC	307 x 111
5	Empty cans ( can size 307 x 112)	307 x 112
6	Empty cans ( can size 307 x 113)	307 x 113
Sheet3

Excel 2007

Cell Formulas
Range	Formula
B3	=MID(A3,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A3&123456789)),9)
B4	=MID(A4,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A4&123456789)),9)
B5	=MID(A5,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A5&123456789)),9)
B6	=MID(A6,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A6&123456789)),9)

 

[ahamed]

hi,

thank you very much,,

It is really working

Regards

Rifkan

 

[bosco_yip]

Ignore my post

Regards