Imperial Weights in charts

[bell_pa]

This has me confused!

I have a sheet with a column of dates and a column of imperial weights that look like "10st 6lb". I need to chart the weights but cant seem to do it. I converted the cell into something I thought Excel would understand by using the following:
=LEFT(B2,FIND("st",B2,1)-1)&"."&MID(B2,FIND("st",B2,1)+3,FIND("lb",B2,1)-1-FIND(" ",B2,1))
but I hadn't realised that Excel would consider 10.11 to be smaller than 10.2. Obviously, I want it to be "ten delimiter eleven" rather than "ten point one one".
I had then considered just converting to pounds or even stones with a decimal but I've been told that isn't neat enough.
The chart needs to show 10st 7lb, 10.7, 10:7 or similar rather than 10.5 (ten and a half) stone.
Does this make sense to anyone?

 

[AlphaFrog]

Would 10st 7lb converted to 10:07 be acceptable?

If yes, you could convert the weights to time.

Excel Workbook
	A	B
1	10st 6lb	10:06
2	10st 11lb	10:11
3	8st 7lb	8:07
...

Cell Formulas
Range	Formula
B1	=TIMEVALUE(SUBSTITUTE(SUBSTITUTE(A1,"st ",":"),"lb",""))
B2	=TIMEVALUE(SUBSTITUTE(SUBSTITUTE(A2,"st ",":"),"lb",""))
B3	=TIMEVALUE(SUBSTITUTE(SUBSTITUTE(A3,"st ",":"),"lb",""))

Format column B at Time 13:30
This only works up to 23 stone.

EDIT: while time would display close to what you ask, it wouldn't scale the same as weight. The minutes would scale 1 to 60. That's not the same as lbs 1 to 14.

 

[alansidman]

here are some other options also.

http://www.mrexcel.com/forum/showthread.php?t=247379

Alan

 

[bell_pa]

Anything that will go into a chart axis will be great. I'll give that a try and let you know. Thanks!

 

[bell_pa]

When i tried the time option it got confused when the weight drops from 10:00 to 09:13. The scale on the chart thinks that 09:59 should be the sequential value directly before 10:00 but I don't want it to be. Cheers anyway!