working with data

[Guest]

I'm using excel 2002. I get a report from a database with the format: "1x
#02-0001/56 ProductA 3x #04-0003/87 ProductB 1x 02-0009/65 ProductC" in one
cell. Where the number before x is the quantity sold, the rest are product
numbers and descriptions. What would I use to answer the following
questions... find all ProductB and return the Quantity (in this case,
quantity is 3). The product may appear in any order and any quantity in the
cell. Thanks for your help.

 

[Guest]

First, I would use Data/Text To Columns using space as the delimiter to get
the data into separate cells. This would put your sample data in cells A1:I1
(make sure there is nothing in B1:I1 when you do this as it will overwrite
existing data in these cells).

Then in J1 (assuming the quantity will always appear 2 columns before the
product description):

=--SUBSTITUTE(INDEX(A1:I1,MATCH("ProductB",A1:I1,0)-2),"x","")

If it is possible Product B may not appear and you want to avoid #N/A errors:

=IF(ISNUMBER(MATCH("ProductB",A1:I1,0)),--SUBSTITUTE(INDEX(A1:I1,MATCH("ProductB",A1:I1,0)-2),"x",""),"")

Then copy down column J.

 

[Guest]

One venture ..

Assuming source data as posted in A1 down,
with products listed in B1 down, ie in B1 is for eg: ProductB

Try in C1
=LEFT(MID(A1,SEARCH(B1,A1)+LEN(B1),5),SEARCH("x",MID(A1,SEARCH(B1,A1)+LEN(B1),5))-1)+0
Copy down as required.

For the sample source data as posted placed in A1
if you have in B1: ProductB,
you'd get in C1: 1

And if you have in B1: ProductA,
you'd get in C1: 3

 

[Guest]

The NG wrapped the OP's example a little funny - looks like the quantity
appears before the Product description, so for ProductB, OP wants 3 and for
ProductA it would be 1 and ProductC would also be 1 (although I assumed the
OP is only looking for ProductB, but finding A and C are the most likely
follow-up questions <g>).

 

[Guest]

Thanks

The NG wrapped the OP's example a little funny - looks like the quantity
appears before the Product description, so for ProductB, OP wants 3 and for
ProductA it would be 1 and ProductC would also be 1 (although I assumed the
OP is only looking for ProductB, but finding A and C are the most likely
follow-up questions <g>).

 

[Max]

Ahh, think you're right there <g>. What was suggested presumed the "x" is
after the "Product". It won't work if the "x" is before the "Product". I
don't have an alternative to suggest here. Cheers.

 

[Guest]

That's what I thought at first, too. Just for the sake of academics (and I'm
trying to avoid doing my chores), if B1=ProductB this appeared to work (no
errorhandling)

C1 (CSE) =MATCH(SEARCH(B1,A1)-2,IF(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="
",ROW(INDIRECT("1:"&LEN(A1)))))

D1 (CSE) =IF(ISNUMBER(MATCH(C1-1,IF(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="
",ROW(INDIRECT("1:"&LEN(A1)))))),MATCH(C1-1,IF(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=" ",ROW(INDIRECT("1:"&LEN(A1))))),1)

E1 =--SUBSTITUTE(MID(A1,D1,C1-D1),"x","")

But that's about as good as I could get it using only a formula approach.
Have a good day/evening (whichever is appropriate <g>).

 

[Max]

Thought it was a marvellous formulas-only rendition, JMB!

 

[Guest]

Thanks Max!

Thought it was a marvellous formulas-only rendition, JMB!