Converting Miles and Chains

[Les]

Is there a function that would convert this:

24M 23½C

To this:

024.0517

Basically that's miles and chains converted to miles and yards with the
removal of the alpha characters and returned in the exact format as shown.

Thanks

 

[Pete_UK]

How many characters similar to ½ will you have? Do you use 1/4 and
3/4 ?

Will you always have M and C in the expression, even if you don't have
any miles (eg 0M 13C)?

Pete

 

[Roger Govier]

Hi Les

provided you always use a 0M, if there are no miles then the following
should work
=IF(CODE(MID(A1,LEN(A1)-1,1))>187,
--(TEXT(LEFT(A1,FIND("M",A1)-1),"000"&
"."&MID(A1,FIND(" ",A1)+1,2)*22+
ROUND((CODE(MID(A1,LEN(A1)-1,1))-187)*5.5,0))),
--(TEXT(LEFT(A1,FIND("M",A1)-1),"000"&
"."&MID(A1,FIND(" ",A1)+1,2)*22)))

The formula is all on one line.
I have broken it up in order that your NG reader does not cause it to break
in funny places.

 

[Roger Govier]

I forgot to say, the cell with the formula needs to be formatted
Format>Cells>Number>Custom> 000.000