Excel Race times Linked sheet

[siony1974]

I'm wondering if we could get an addition to the output from Gruss BA when it is linked to an Excel sheet.

Cell A1 holds all the race info and using formulas I'm able to extract the race time and other race info.

The problem is that having to use MID and SEARCH to extract the race time causes headaches later on. Once I have the race time I use VLOOKUP to match a race in a different sheet to trigger a bet. The way we have to extract the race times plays havoc with the vlookups as you have to manipulate the data types to suit the data type of the extracted race time. If I copy/paste race times into my sheet and those times have taken on the format of where it came from it can completely knacker the vlookup. When you put 16:40 into Excel it automatically wants to assign it a Time format. You can't do that as the extracted race time info is seen by Excel as Text. It doesn't matter which custom time format you try and assign to that cell it just won't work as a Time format as part of a vlookup.

In the BA output to Excel cell D1 holds the next race time in a beautiful static time format.

Please, please, please could you add the current race time in cell C1,E1 or anywhere you like really in a similar beautiful time format.

The linked sheets are fantastic for creating tailored triggers for races but having to extract the race time and the data type problems it brings is doing my head in. I'm missing entire days of racing because the vlookups are so sensitive to data type. Having to make sure they're all either set to general or text seems daft when adding the current race time in a cell of its own would be so much simpler.

[Captain Sensible]

Seems your problems are more down to how excel is handling your data than the way BA presents it, have you considered turning off the autoformat options in excel so excel doesn't try to predict the format it thinks your data is in and leaves it up to you to set it as text, time etc

To turn it off you simply go to File -> Options-> Advanced and uncheck the option for "Extend data range formats and formulas."

Otherwise there's plenty of snippets of VBA around the site where you can pick out times, distances etc from the A1 cell.

If you're OK with VBA just open the editor and add the following to a module

Code: Select all

Function FindWord(Source As String, Position As Integer)
Dim arr() As String
arr = VBA.Split(Source, " ")
xCount = UBound(arr)
If xCount < 1 Or (Position - 1) > xCount Or Position < 0 Then
    FindWord = ""
Else
    FindWord = arr(Position - 1)
End If

End Function


You can then use that anywhere in your excel sheet, or vlookup, just by typing the following in a cell, it'll just give you the 5th word in cell A1

Code: Select all

=findword(A1,5)

[siony1974]

Many thanks for the reply Captain.

I don't have any trouble extracting the data I need from Cell A1 it's just that the time info gained from using MID and SEARCH can be a pain once it's extracted. Many thanks though for the VBA , that will be very useful.
I seem to have got round it by using LEFT and RIGHT on the extracted race time into two cells then adding a third cell with 00 in it. I then used the TIME function to put it all back together. =TIME(cell1,cell2,cell3) It didn't like it when I tried using concatenate.
I set the custom time format to hh:mm:ss and now it all seems to work fine. All my vlookups seem to be happy with the time in that format.
Happy days are here again.

[Captain Sensible]

Good to see you got it sorted, there's plenty of ways to skin a cat so to speak, take a look at the TIMEVALUE function as that should save all the hassle adding three cells together for you or something like

=TIME(MID(A1,FIND(":",A1)-2,2),MID(A1,FIND(":",A1)+1,2),0)

Will pull out the date then just forat the cell as you please