Extract values before & after character while ignoring text

[mst3kr]

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!

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Try this

21 09 10.xlsm
	A	B	C
1
2	1000	10/2 lb	20000
3	480	20/2.5 lb	24000
4	750	15/3.5 lb	39375
lbs

Cell Formulas
Range	Formula
C2:C4	C2	=A2*LEFT(B2,FIND("/",B2)-1)*REPLACE(LEFT(B2,FIND(" ",B2)),1,FIND("/",B2),"")

 

[mst3kr]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Try this

21 09 10.xlsm
	A	B	C
1
2	1000	10/2 lb	20000
3	480	20/2.5 lb	24000
4	750	15/3.5 lb	39375
lbs

Cell Formulas
Range	Formula
C2:C4	C2	=A2*LEFT(B2,FIND("/",B2)-1)*REPLACE(LEFT(B2,FIND(" ",B2)),1,FIND("/",B2),"")

Thanks Peter! I'd completely forgotten to update my user profile. Thanks for the reminder...

I tried your formula and it works perfectly! Thank you very much! This will save me a ton of time manipulating existing formulas or cells to get what the results I needed...

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

Also thanks for updating your profile.