ExcelBanter - View Single Post

Joikochan

Thanks

"JMB" wrote:

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" wrote:

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",M ID(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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Joikochan" wrote:
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.