extracting numbers from text

[paramnayak]

dear all,
i want to extract the numbers from a given text.

the text is of this form
22 boxes X 45 pcs
17 bags X 12 rolls
14 c/b x 500 r

i need to get 22 and 45 from 1 st example and similarly 17& 12 from second and 14 & 500 from third.

what can be the formula ?

 

[NARAYANK991]

Hi Nayak ,

The easiest way is to use the Text to Columns feature of Excel , since you can use the space character as a delimiter.

Narayan

 

[paramnayak]

but I need to find the quantity in the next cell . hence i will need a formula to multiply 22 and 45

 

[bobhc]

Good day paramnayak

The search box does work, people seem to think it does not.


http://chandoo.org/wp/2012/06/19/extract-numbers-from-text-excel/
http://office.microsoft.com/en-us/e...rs-from-alphanumeric-strings-HA001154901.aspx

Your description of your problem is not very clear, the answer given by Narayan does what you asked, then you add to your OP by expanding the problem, if you do text to columns the quantities will be in cells and you can do a simple function to multiply the cells required.
Or if that is not what you are after read the links and see if they help.

 

[Haseeb A]

Hello Nayak,

Assuming text is in A1 to down. try this in B1 & copy down.

=LEFT(A1,FIND(" ",A1)-1)*LOOKUP(100^100,MID(A1,SEARCH(" X ",A1)+3,ROW(A$1:A$15))+0)