Formula to convert stone and pounds into pounds

[Ironman]

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):

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!

 

[Phuoc]

Try this:

=IFERROR(14*LEFT(F64,SEARCH("st",F64)-1),0)+IFERROR(LOOKUP(100,--MID(F64,SEARCH("lbs",F64)-1,{1,2})),0)

 

[Michael M]

I'm sure there is a better way....

=LEFT(B2,FIND(" st",B2,1))*14+MID(B2,SEARCH("st ",B2)+2,SEARCH(" lbs",B2)-SEARCH("st ",B2)-1)+0

 

[rlv01]

Another option is to use a User Defined Function

'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

 

[Ironman]

Haha, thank you so much Phuoc, Michael (once again!) and rlv01 (again!) I'm spoilt for choice here!

 

[Peter_SSs]

"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
	D	E
1
2	10st 10lbs	150
3	0st 10lbs	10
4	8st 3lbs	115
5	8st 11lbs	123
6	12st 0lbs	168
Convert

Cell Formulas
Range	Formula
E2:E6	E2	=LEFT(D2,FIND("s",D2)-1)*14+LEFT(RIGHT(D2,5),2)

 

[Ironman]

Hey Peter, thank you so much once again, looks like you saved the most elegant solution till last

 

[Peter_SSs]

Hey Peter, thank you so much once again

You're welcome once again.