• Hi Guest Just in case you were not aware I wanted to highlight that you can now get a 20% discount on Inform Racing.
    Simply enter the coupon code ukbettingform when subscribing here.
    We have a lot of members who are existing users of Inform Racing so help is always available if needed.
    Best Wishes
    AR
  • Hi Guest Just in case you were not aware I wanted to highlight that you can now get a free 7 day trial of Horseracebase here.
    We have a lot of members who are existing users of Horseracebase so help is always available if needed, as well as dedicated section of the fourm here.
    Best Wishes
    AR

formula

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 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
Not up on VBA but I will give it a go.
Many thanks
 
Are 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.
 
Apologies for the delayed response, but I haven't visited here for a while . . .

Referring back to the original post, a way to do it using Excel logic is to pick out the figure for "1", the figure for "5" and the figure for "147" and covert all to yards, add together and then divide the sum by 220 - to give an answer in furlongs with the yardage expressed as a decimal.

To pick out the "1", identify the position of the "m" character in the string (using the FIND() function) and reference the digit before this:
so if "m" is returned as character 2 (using a separate cell to capture this), the miles would be:
=MID(cell ref of string, cell ref of character "m" position in string -1, 1)
(1 in the third criteria would be a constant as races don't tend to be over distances that take the miles to double figures)

Repeat for the furlongs (again, only a single digit value is valid, so 1 again as the third criteria)

Yards are more problematic as a single, double and treble digit value are all valid, although there are several work arounds to this. One way would be to identify the position of the characters "f" and "y" and the number of characters between these will tell you whether the yards value is 1, 2 or 3 characters in length. Once known, and all of the answers have been dropped into individual cells, write a MID function statement thus:

=MID(cell ref of string, position of character "f" +1, cell ref of where number of characters in yardage is detailed - from above).


When all done, hide away the columns where the multi-stage logic functions live, multiply the "m" value by 1760, the "f" value by 220, the "y" value by 1, add together and divide the sum by 220 to give total furlongs expressed as a decimal. Garnish to taste and serve.

Note, you may need to modify the logic with some error traps as sometimes the "m", "f" and/or "y" values may not be there. This is easily done using the IF function, ie if the character number of "m" returns an error, enter zero, else enter the character number:
=IF(ISERROR(FIND(cellref,"m",1)),0,FIND(cellref,"m",1)) think that's right . . . ?????? :)

If there is no value for "f" (as no furlongs) the "f"+1 in the blue example above will need to be altered to m+1 - if both "f" and "m" are missing it would mean that the race was being run over 219 yards which seems improbable. The fact that you have to account for every possible combination of race distances (miles, furlongs, yards; miles and furlongs; miles and yards; fulongs and yards; just miles on the dot, just furlongs on the dot) makes it seem a lot more complicated than it actually is.

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.


As a footnote, a relative of mine is doing a fundraising event for an international charity, and if you'd like to donate a fiver via her justgiving page I'll write it for you ! ! :D
 
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.
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.

AR
 
Fair comment . . . it's possible to autorun anything (on file opening, switching to a specific sheet within a workbook, data entry into a cell, whatever) but the function has still got to execute in order to take the input data and create the output?
 
but the function has still got to execute in order to take the input data and create the output?
Yes it does and as before that is no different to using a built in worksheet function such as IF or FIND. You simply type

=StringToDistance(A2) in the cell you want to convert the string in A2 to the distance in furlongs.

No different from typing something like =IF(A2>A1, 1,0) ie there is no need for any other code to get the result, if the IF function works 'on its own' then my UserDefinedFunction will do the same.
 
ArkRoyal ArkRoyal would that get ride of the 1/2 (ie 5 1/2f)as well as iam having a problem that excel wont convert the 1/2 and there is no setting in HRB to do it
 
ArkRoyal ArkRoyal found it(not my work ). someone one day may need this so this is how to change the 1/2f that comes in the hrb report to .5(I am using it to see at what distance my winners come in at)
Select ½ just this character in a string and press CTRL-C to copy it to the clipboard
Select the range where you want to perform the substitution (so column G)
invoke the Find/Replace dialog(ctrl+h)
in the Find textbox press CTRL-V (to bring back the character)
in the Replace with write ".5"
 
Back
Top