David Punshon
Yearling
Does anyone know an excel formula to convert distances ie 1m5f147y into 13.5
Function StringToDistance(StrDistance As String) As Double
Dim arDistance As Variant
Dim Miles As Integer
Dim Furlongs As Integer
Dim Yards As Integer
arDistance = Split(StrDistance, "m")
Miles = arDistance(0)
arDistance = Split(arDistance(1), "f")
Furlongs = arDistance(0)
arDistance = Split(arDistance(1), "y")
Yards = arDistance(0)
StringToDistance = Miles * 8 + Furlongs + (Yards / 220)
End Function
Not up on VBA but I will give it a go.Do you know VBA?
This will convert the distances in the form you have requested ie 1m5f147y or 2m5f110y etc but will need modifying to do cope with distances like 1m67y
Code:Function StringToDistance(StrDistance As String) As Double Dim arDistance As Variant Dim Miles As Integer Dim Furlongs As Integer Dim Yards As Integer arDistance = Split(StrDistance, "m") Miles = arDistance(0) arDistance = Split(arDistance(1), "f") Furlongs = arDistance(0) arDistance = Split(arDistance(1), "y") Yards = arDistance(0) StringToDistance = Miles * 8 + Furlongs + (Yards / 220) End Function
Do you have to create a new page for thisNot up on VBA but I will give it a go.
Many thanks
I take from HRB I am just being lazy lolAre you pasting from Racing Post or Sporting Life site? If so you will have to sort the figures out first before you can convert them.
hi David PunshonDo you have to create a new page for this
Thats what the function posted above does.If so you will have to sort the figures out first before you can convert them.
Not true, you do not have to run the routine I posted earlier it is simply a user defined function and is called and utilised in exactly the same way as you are using say FIND or IF.As I'm sure you're aware, using logic rather than VBA means you haven't got to run a routine to extract the answer - as soon as the raw data is dropped into a spreadsheet the conversion is instant, and any comparisons or subsequent processing equally instant.
Yes it does and as before that is no different to using a built in worksheet function such as IF or FIND. You simply typebut the function has still got to execute in order to take the input data and create the output?