Formula to convert stone and pounds into pounds

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
1,069
Office Version
  1. 365
Platform
  1. Windows
Hi

Many years ago I was kindly given a formula to convert pounds into stone and pounds in a single cell (1 stone = 14 pounds):

Excel Formula:
CONCATENATE(ROUNDDOWN(C64/14,0), "st ", MOD(C64,14), "lbs")

Now I need to do the opposite i.e. convert stone and pounds into pounds and I can't work out from the above formula what needs to be reversed!

I understand that since the above formula was written, Excel now has a convert function but I still can't see how to do this particular conversion.

The other issue I have is how to enter stone and pounds into a single cell in the custom number format "00st 0lbs" so it can be converted to pounds - would it be better to use 2 cells for stones and pounds?

Help would be appreciated - many thanks!
 
Last edited:

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Try this:

=IFERROR(14*LEFT(F64,SEARCH("st",F64)-1),0)+IFERROR(LOOKUP(100,--MID(F64,SEARCH("lbs",F64)-1,{1,2})),0)
 
Upvote 0
I'm sure there is a better way....
Excel Formula:
=LEFT(B2,FIND(" st",B2,1))*14+MID(B2,SEARCH("st ",B2)+2,SEARCH(" lbs",B2)-SEARCH("st ",B2)-1)+0
 
Upvote 0
Another option is to use a User Defined Function

VBA Code:
'UDF to convert "00st 0lbs" to pounds
Function SP2P(StLbs As String) As Double
    Dim SA As Variant

    SA = Split(Application.Trim(Replace(Replace(StLbs, "st", " "), "lbs", "")), " ")

    SP2P = CDbl(SA(0)) * 14 + CDbl(SA(1))
End Function
 
Upvote 0
Haha, thank you so much Phuoc, Michael (once again!) and rlv01 (again!) I'm spoilt for choice here!
 
Upvote 0
"00st 0lbs"
If your weights are always text in that format (or "0st 0lbs" or "0st 00lbs" or "00st 00lbs") then I think this should suffice.

21 09 07.xlsm
DE
1
210st 10lbs150
30st 10lbs10
48st 3lbs115
58st 11lbs123
612st 0lbs168
Convert
Cell Formulas
RangeFormula
E2:E6E2=LEFT(D2,FIND("s",D2)-1)*14+LEFT(RIGHT(D2,5),2)
 
Upvote 0
Solution
Hey Peter, thank you so much once again, looks like you saved the most elegant solution till last :)
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,297
Members
448,564
Latest member
ED38

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