Converting Miles and Chains

  • Thread starter Thread starter Les
  • Start date Start date
L

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
 
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
 
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.
 
I forgot to say, the cell with the formula needs to be formatted
Format>Cells>Number>Custom> 000.000
 
Back
Top