Extract and Sum certain numbers from a cell that contains both text and numbers I don't need

[BethLivingston]

HI all. This is my first post here.
I have a client who is a retail furniture store and I am importing their POS export to their web store.
The POS system exports the inventory by showroom but all in one cell like this:

6/12SHW;1/16SHW;1/16SHW;1/16SHW;3/16SHW;6/13SHW;1/14SHW;1/14SHW;1/14SHW;1/14SHW;1/14SHW;1/14SHW;
or this

1/16SHW;1/12SHW;1/13SHW;

The number before the / is the inventory at that showroom. The number and text between the / and the semi-colon is the showroom number.
I need to extract only the numbers before the / and sum them to get the total inventory for that product.

I've searched for a solution and there seems to be one. I just can't figure out how to put the various functions together to make this work.

Any ideas?
Thanks in Advance
Beth

 

[Rick Rothstein]

Assuming your value is in cell A1 and further assuming the entry in that cell will not be longer than 300 characters and that the maximum number of inventory items in that cell is 25, then this formula will return the sum of all the number in front of a slash...

=SUMPRODUCT(0+(0&TRIM(MID(SUBSTITUTE("/"&SUBSTITUTE(A1,";","/"),"/",REPT(" ",300)),(2*ROW(1:25)-1)*300,300))))

Note: The assumptions I made (since you didn't give us a hint, I had to guess) can be modified within certain limits if needed.

 

[BethLivingston]

Thank you Rick,
That works. However, when I copy the formula to the cells below, it changes the value in the (2*ROW(1:25) part to (2*ROW(2:26), (2*ROW(3:27) and so forth which screws up the calc. Is there a way I can copy this to the subsequent rows without having that happen? Today I am testing with 300 products but when I actually do this it will be thousands of products so changing this manually in each cell would be a bear.

Thanks so much for your help!

 

[Rick Rothstein]

What happened to my $ signs? I've noticed that under certain modifications to formulas, Excel drops $ signs from range references. I haven't figured out what those conditions are though. My original formula had them and I did not think to look to see if they were still there. Here is the formula the way I intended to post it...

=SUMPRODUCT(0+(0&TRIM(MID(SUBSTITUTE("/"&SUBSTITUTE(A1,";","/"),"/",REPT(" ",300)),(2*ROW($1:$25)-1)*300,300))))

 

[BethLivingston]

Holy cow! That's perfect! I'm going to look like a rock star to this client!

Thank you!
Beth