Possible Index Formula

[Celticshadow]

Hi All

I have a list of horses as attached with their betting forecast prices. What I would like to do is extract just the prices associated with those horses and place them in column B.

Thanks in advance.

Kind Regards

https://spreadsheets.google.com/spr...ffBUodHZ3anBXc0t6d0ZHVUZxMnVHUmQxTkE&hl=en_US

 

[T. Valko]

Hi All

Does anyone have any ideas as to a possible solution.

Thanks in advance.

Kind Regards

Try this...

Book1
	A	B	C
1	Horse Name	Forecast	Value
2	Crocus Rose	100/30	3.33
3	Praxiteles	13/8	1.63
4	Arab League	11/2	5.5
5	Pelham Crescent	17/2	8.5
6	Parhelion	16/1	16
7	Record Breaker	9/1	9
8	Charmeur	17/2	8.5
9	Sansili	14/1	14
Sheet1

Cell A12 contains this string:

BETTING FORECAST: 13/8 Praxiteles, 100/30 Crocus Rose, 11/2 Arab League, 17/2 Charmeur, 17/2 Pelham Crescent, 9/1 Record Breaker, 14/1 Sansili, 16/1 Parhelion.

Enter this formula in B2:

=TRIM(RIGHT(SUBSTITUTE(LEFT(A$12,SEARCH(A2,A$12)-2)," ",REPT(" ",100)),100))

Enter this formula in C2:

=ROUND(LEFT(B2,FIND("/",B2)-1)/MID(B2,FIND("/",B2)+1,5),2)

Select both B2 and C2 and copy down as needed.

 

[Celticshadow]

Hi T Valko

Many thnaks for replying. Once again you superb knowledge has solved another excel problem, I am very grateful for your help and you have shoed yourself to be a credit to the board once more.

Kind Regards

 

[T. Valko]

Hi T Valko

Many thnaks for replying. Once again you superb knowledge has solved another excel problem, I am very grateful for your help and you have shoed yourself to be a credit to the board once more.

Kind Regards

You're welcome. Thanks for the feedback!

 

[Celticshadow]

Hi All

I dont seem to have got the html editor right but hopefully the below request is clear enough. I would be grateful if someone could provide a formual to the below problem. Thanks in advance.

Kind Regards

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       <table width="927" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="mso-width-source:userset;mso-width-alt:5705;width:117pt" width="156"> </colgroup><colgroup><col style="mso-width-source:userset;mso-width-alt:3401;width:70pt" width="93"> </colgroup><colgroup><col style="mso-width-source:userset;mso-width-alt:2889;width:59pt" width="79"> </colgroup><colgroup><col style="width:48pt" width="64"> </colgroup><colgroup><col style="mso-width-source:userset;mso-width-alt:5522;width:113pt" width="151"> </colgroup><colgroup><col style="width:48pt" span="6" width="64"> </colgroup><tbody><tr style="height:12.75pt" height="17"><td style="height:12.75pt;width:117pt" width="156" height="17">
</td><td style="width:70pt" width="93">
</td><td style="width:59pt" width="79">
</td><td style="width:48pt" width="64">
</td><td colspan="3" class="xl71" style="width:209pt" width="279">Require Data</td><td style="width:48pt" width="64">
</td><td style="width:48pt" width="64">
</td><td style="width:48pt" width="64">
</td><td style="width:48pt" width="64">
</td></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17">
</td><td>
</td><td>
</td><td>
</td><td class="xl70">Horse</td><td class="xl70">Fraction</td><td class="xl70">Decimal</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17">Aye Aye Digby 22 </td><td class="xl70">7/1</td><td class="xl70">7</td><td>
</td><td class="xl70">Aye Aye Digby 22 </td><td class="xl70">7/1</td><td class="xl69">7.00 </td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17">Night Trade 10 </td><td class="xl70">11/2</td><td class="xl70">5.5</td><td>
</td><td class="xl70">Night Trade 10 </td><td class="xl70">11/2</td><td class="xl69">5.50 </td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17">Muffraaj 32 </td><td class="xl70">6/1</td><td class="xl70">6</td><td>
</td><td class="xl70">Muffraaj 32 </td><td class="xl70">6/1</td><td class="xl69">6.00 </td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17">Sharpened Edge 52 </td><td class="xl70">16/1</td><td class="xl70">16</td><td>
</td><td class="xl70">Sharpened Edge 52 </td><td class="xl70">16/1</td><td class="xl69">16.00 </td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17">Mi Regalo 238 </td><td class="xl70">(IRE),</td><td class="xl70">#VALUE!</td><td>
</td><td class="xl70">Mi Regalo 238 </td><td class="xl70">12/1</td><td class="xl69">12.00 </td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17">Dream Catcher 11 </td><td class="xl70">Judgement,</td><td class="xl70">#VALUE!</td><td>
</td><td class="xl70">Dream Catcher 11 </td><td class="xl70">25/1</td><td class="xl69">25.00 </td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17">Rash Judgement 26 </td><td class="xl70">25/1</td><td class="xl70">25</td><td>
</td><td class="xl70">Rash Judgement 26 </td><td class="xl70">25/1</td><td class="xl69">25.00 </td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17">Jungle Bay 11 p </td><td class="xl70">Interakt,</td><td class="xl70">#VALUE!</td><td>
</td><td class="xl70">Jungle Bay 11 p </td><td class="xl70">8/1</td><td class="xl69">8.00 </td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17">Alpha Delta Whisky 32 </td><td class="xl70">20/1</td><td class="xl70">20</td><td>
</td><td class="xl70">Alpha Delta Whisky 32 </td><td class="xl70">20/1</td><td class="xl69">20.00 </td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17">Interakt 19 </td><td class="xl70">8/1</td><td class="xl70">8</td><td>
</td><td class="xl70">Interakt 19 </td><td class="xl70">8/1</td><td class="xl69">8.00 </td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17">Silenzio 8 6x </td><td class="xl70">4/1</td><td class="xl70">4</td><td>
</td><td class="xl70">Silenzio 8 6x </td><td class="xl70">4/1</td><td class="xl69">4.00 </td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17">Desert Icon 7 </td><td class="xl70">12/1</td><td class="xl70">12</td><td>
</td><td class="xl70">Desert Icon 7 </td><td class="xl70">12/1</td><td class="xl69">12.00 </td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17">Stonecrabstomorrow 13 </td><td class="xl70">10/1</td><td class="xl70">10</td><td>
</td><td class="xl70">Stonecrabstomorrow 13 </td><td class="xl70">10/1</td><td class="xl69">10.00 </td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17">
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:12.75pt" height="17"><td colspan="11" style="height:12.75pt;mso-ignore:colspan" height="17">Betting   Forecast: 4/1 Silenzio, 11/2 Night Trade (IRE), 6/1 Muffraaj, 7/1 Aye Aye   Digby (IRE), 8/1 Interakt, Jungle Bay, 10/1 Stonecrabstomorrow (IRE), 12/1   Desert Icon (IRE), Mi Regalo, 16/1 Sharpened Edge, 20/1 Alpha Delta Whisky,   25/1 Rash Judgement, Dream Catcher (FR)</td></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17">
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="mso-height-source:userset;height:12.75pt" height="17"><td colspan="11" rowspan="6" class="xl72" style="height:76.5pt;   width:695pt" width="927" height="102">I need another formula to extract the betting forecast prices   and match them to the above list of horses. The formula that was kindly given   before does most of the work but unfortunately this forecast has in brackets   where the horse originates from and some of the cells are showing errors as   above. I would like it to show as to the right.</td></tr><tr style="height:12.75pt" height="17"></tr><tr style="height:12.75pt" height="17"></tr><tr style="height:12.75pt" height="17"></tr><tr style="height:12.75pt" height="17"></tr><tr style="height:12.75pt" height="17"></tr> </tbody></table>

 

[Celticshadow]

Hi Again

I have uploaded a sheet as below just in case thing were not so clear - I do seem to struggle with the HTML editor (apologies for that).

Kind Regards

https://docs.google.com/spreadsheet/ccc?key=0AhsVc2uffBUodDREa3V2MExZLWxSQjNPQk9FbzJ0aVE&hl=en_US

 

[T. Valko]

Hi Again

I have uploaded a sheet as below just in case thing were not so clear - I do seem to struggle with the HTML editor (apologies for that).

Kind Regards

https://docs.google.com/spreadsheet/ccc?key=0AhsVc2uffBUodDREa3V2MExZLWxSQjNPQk9FbzJ0aVE&hl=en_US

I'm not sure how you'd do this now.

Before there was a specific pattern that could be exploited.

Find the horse name and work to the left until you find a space character, just before that space was the odds. But with this new data that pattern is not consistent.

Sorry, I have no suggestions for this.

 

[Celticshadow]

Hi T.Valko

Many thanks for taking a look. I do not know why they add the horse origin to the forecast as it is double counting because it is already show in the card itself. Not to worry you have been most helpful with many of my previous requests, so thanks again.

Kind Regards

 

[T. Valko]

Hi T.Valko

Many thanks for taking a look. I do not know why they add the horse origin to the forecast as it is double counting because it is already show in the card itself. Not to worry you have been most helpful with many of my previous requests, so thanks again.

Kind Regards

Try starting a new thread.

Maybe someone can come up with a UDF that can do this.

Good luck!

 

[Celticshadow]

Hi TValko

Sounds like a plan but unfortunatley I know absolutely nish about coding but thats entirely my fault.

Kind Regards

 

[T. Valko]

Hi TValko

Sounds like a plan but unfortunatley I know absolutely nish about coding but thats entirely my fault.

Kind Regards

Well, I'm not the best programmer but someone that really knows what they're doing can write a custom function (UDF = User Defined Function) so that it's used just like any worksheet function.

Go ahead and give it try. You got nothing to lose!