Extract values before & after character while ignoring text

mst3kr

New Member
Joined
Apr 15, 2013
Messages
46
Office Version
  1. 2019
  2. 2010
  3. 2007
Platform
  1. Windows
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!
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
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
ABC
1
2100010/2 lb20000
348020/2.5 lb24000
475015/3.5 lb39375
lbs
Cell Formulas
RangeFormula
C2:C4C2=A2*LEFT(B2,FIND("/",B2)-1)*REPLACE(LEFT(B2,FIND(" ",B2)),1,FIND("/",B2),"")
 
Upvote 0
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
ABC
1
2100010/2 lb20000
348020/2.5 lb24000
475015/3.5 lb39375
lbs
Cell Formulas
RangeFormula
C2:C4C2=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...
 
Upvote 0
You're welcome. Thanks for the follow-up. :)

Also thanks for updating your profile. (y)
 
Upvote 0

Forum statistics

Threads
1,224,522
Messages
6,179,299
Members
452,904
Latest member
CodeMasterX

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top