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

[EduardStoo]

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

[AlKey]

Hi Stuart,

Could you please provide more samples so we could have a better understanding about your data?

[WideBoyDixon]

Tricky! Easier with a UDF I'd say. However, this will extract the last "word" from the cell that occurs before the complete words "KG", "L", "LITRE" or "LITRES":

Please Login or Register  to view this content.

It's an array formula so you'll need to enter it with [Ctrl]+[Shift]+[Enter]

Hope it gets you in the right direction. If not, I think UDF would be the way to go.

WBD

[MrShorty]

Is it necessary for the entire calculation to occur in B1? If not, I would proceed something like this (beware -- several helper columns ahead):

1) Text to columns using space as delimiter to get each "word" in a separate cell in the row. If the final output must be in B1, then have the Text to columns output to column G or something well out of the way to the right.
2) A series of MATCH() functions to find the cell with the "unit" in it. =MATCH("kg",text to columns output,0) similar for other units to be searched for.
3) An INDEX() function that uses the result of step 2 to return the cell value immediately before the "units" text. =INDEX(text to columns output,-1+result of 2). If the result must be in B1, then this formula goes in B1.

It is far from fully developed, because I don't know if you are open to this kind of approach. If you are, then we can develop it in more detail.

[tim201110]

=--substitute(mid(a1,2+aggregate(14,6,row(indirect("1:"&len(a1)))/(mid(a1,row(indirect("1:"&len(a1))),1)="-"),1),3)," ","")

[EduardStoo]

Thanks both -

Attached is a basic catalogue entry example, hope this gives a better idea

Thanks,

Stuart

[EduardStoo]

Thanks also Tim201110. I have to leave the office in a mo but will try again with this ASAP

Thanks

[EduardStoo]

Hi All -

Thanks for your help but the two formulas do not seem to work -

=--substitute(mid(a1,2+aggregate(14,6,row(indirect("1:"&len(a1)))/(mid(a1,row(indirect("1:"&len(a1))),1)="-"),1),3)," ","")

...throws up a #NUM! error and

=TRIM(RIGHT(SUBSTITUTE(LEFT($A1,MIN(IFERROR(SEARCH({" KG "," L "," LITRE "," LITRES "},$A1&" "),LEN($A1)+1))-1)," ",REPT(" ",LEN($A1))),LEN($A1)))

...just shows a blank return to all cells.

WideBoyDixon - are you referring to User Defined Fields when you say UDF? There are lots of these built in to the spreadsheet but I am trying to work the formula to correct the 15-20% of the UDF results where UDFs are blank or incorrect basically.

Mr Shorty - totally a possibility. I already have 35 columns so another few won't hurt. The sheet's very sluggish but I only need to get to the end and then convert all fields to plain text, then job done, would be worth the pain .

Thanks,

Stuart

[WideBoyDixon]

Strange. It's an array formula - did you use Ctrl+Shift+Enter? I was referring to a user-defined function written in VBA.

WBD

[EduardStoo]

Ah! Spotted it, It's where the descriptor says 'KGS' not 'KG', so will build that in, plus others of this ilk!, thanks WBD

[EduardStoo]

Works a treat now. Thanks for help everyone!!

[WideBoyDixon]

Good to hear! Please mark the thread as SOLVED.

WBD