I have weekly data I'm updating where total pounds of product (lbs) needs to be calculated based on the number of units in a box multiplied by the total weight of each unit in that box (Units/lbs).
Below is an example of how the data for Cases & Units/lbs will always come back in the data, with lbs being the calculation of cases multipled by the number of units and weight per unit. Thus, the first item would be 1,000 cases times 20 lbs per case (10 units times 2 lbs each), equaling 20,000 pounds. The second item would be 480 cases times 50lbs per case (20 units * 2.5 lbs), totalling 24,000 pounds.
Cases Units/lbs lbs
1,000 10/2 lb 20,000
480 20/2.5 lb 24,000
750 15/3.5 lb 39,375
My issue is in cleanly pulling the number of units & per unit weight to multiply against the cases due to this cell having the "lb" text in it. I have a formula, =A2*(MID(A2,FIND("/",A2)+1,2)*LEFT(A2, FIND("/", A2)-1)) to grab the Units & lbs values but because the weight per unit will be 1 to 4 digits, I haven't found a way to pull the full values to the right of the "/" without capturing the "lb".
I've I would like to keep from using VBA, if possible. Is there a way to make a dynamic formula to get what I'm after?
Thanks in advance for any help!
Below is an example of how the data for Cases & Units/lbs will always come back in the data, with lbs being the calculation of cases multipled by the number of units and weight per unit. Thus, the first item would be 1,000 cases times 20 lbs per case (10 units times 2 lbs each), equaling 20,000 pounds. The second item would be 480 cases times 50lbs per case (20 units * 2.5 lbs), totalling 24,000 pounds.
Cases Units/lbs lbs
1,000 10/2 lb 20,000
480 20/2.5 lb 24,000
750 15/3.5 lb 39,375
My issue is in cleanly pulling the number of units & per unit weight to multiply against the cases due to this cell having the "lb" text in it. I have a formula, =A2*(MID(A2,FIND("/",A2)+1,2)*LEFT(A2, FIND("/", A2)-1)) to grab the Units & lbs values but because the weight per unit will be 1 to 4 digits, I haven't found a way to pull the full values to the right of the "/" without capturing the "lb".
I've I would like to keep from using VBA, if possible. Is there a way to make a dynamic formula to get what I'm after?
Thanks in advance for any help!