RE: Extracting Certain Numbers from a Text String Containing Several Numbers

Hi -

I was wondering if anyone could help me on this.

We have a list of products in a catalogue whereby we need to extract the pack sizes from the given description. At present using this formula:

=MID(SUMPRODUCT(--MID("01"&REF!,SMALL((ROW($1:$300)-1)*ISNUMBER(-MID("01"&REF!,ROW($1:$300),1)),ROW($1:$300))+1,1),10^(300-ROW($1:$300))),2,300)

...we can successfully pull this through, BUT this is also bringing across and combining a lot of the product names as these also contain numbers, as they quote chemical names in the same description.

EXAMPLE:

Cell A1 = ORGANIC MIX 5-5-5 - 10 KG SACK
(Product Name)

We need Cell B1 to take the '10' from the 10 KG above (using the above formula, the cell B1 brings through '55510', not '10'.

Does anyone know how to make the formula (we have tried various different LEFT/MID/RIGHT based formulas too) pull through the '10' by looking if it is followed by a 'KG', 'L', or 'LITRE' / 'LITRES' please?

We have 220,000 product lines that's all!

Thanks, any guidance appreciated

Stuart