Excel "automatic transform into date"

[Dices]

Now its my turn again.

I copy-paste from RPS the table with the winners of a course and at the table there is a column with their SP. They have the English format ie 7/1 , 10/1 , 15/2.
1. When copied into excel they are transformed into dates, ie 7/1 to 7-Jan, 10/1 to 10-Jan.
How can avoid this? I want the 7/1 odds to be recorded as 8.00 odds etc.
I have the feeling that it would be difficult.
2. The 100/1 or 66/1 odds are recorded as 100/1 and 66/1 but as text?Defently not as number. How can i change this?

Thanks, Dices

[GotaFancy?]

1) If you paste special, can you define the format?
2) "100/1" isn't a number - it cannot be stored as a number - you'll need to import it as text I think, then make transformations in another column into the number you want (whtat number would you want stored for "100/1"? 100?

[Dices]

1. I can special copy as html wich gives me the same problem or special copy as text but at this case i dont get a table of data but a column of data and i cant extract what i want.
The 100/1 odds i want it to be recoded as 101.00 odds (European odds ->your winnings plus your stake 100+1=101). I maybe can do this using some replace .....

This is what i get with special copy as text:

TRAINER Age Wgt JOCKEY SP OR TS RPR

1 Joe Lively (IRE) C L Tizzard 8 10-10 Joe Tizzard 16/1 0 — —

2 7 Levitski Jonjo O/Neill 6 10-10 t A P McCoy 3/1 108 — —

3 6 Cave Of The Giant (IRE) T D McCarthy 5 10-10 Jamie Moore 5/4F 107 — —

4 19 Comeintothespace (IRE) K J Burke 5 10-3 Mr E Cookson(7) 33/1 0 — —

5 8 Over Ice Karen George 4 10-1 S E Durack 20/1 0 — —

6 5 Oakley Absolute J C Fox 5 10-10 Robert Thornton 14/1 0 — —

F Daramoon (IRE) Mrs Norma Pook 6 9-13 1 S P Walsh(5) 100/1 0 — —

PU Super Sensation (GER) G L Moore 6 11-5 b Eamon Dehdashti(5) 7/2 110 — —

PU Sharp Duo (IRE) M B Shears 4 10-8 t Gerry Supple 100/1 0 — —

PU Tipton Rise O J Carter 11 9-10 Mr C Wallis(7) 100/1 0

[slapdash]

I hate software that "thinks" it knows what you want!

I don't use Microsoft Office programs unless I have no choice, but I once had
to write something in Word that had a serial number like 17653246TH in it. It
took me ages to figure out how to tell it I didn't want the "th" as a superscript!

And don't get me started on spell-checkers ...

[georgej]

I had this problem when trying to copy massey results in a table.

Copy the data and paste into word
select all, copy the data from word and pastespecial richtext format
select all, copy, then goto excel
format the columns as text, then pastespecial as text
(I have a spare sheet i won't use just for this - formatted as text)
Note - you will still need to format any column that the 7/1 is going into as text.

I put shortcuts for macros in word and excel for this
This is for Word (i store it in the normal.doc so it is available on opening word)

Sub web2excel()
'
' web2excel Macro
'
    Application.ScreenUpdating = False
    Selection.PasteAndFormat (wdPasteDefault)
    Selection.WholeStory
    Selection.Copy
    Selection.PasteSpecial datatype:=wdPasteRTF
    Selection.WholeStory
    Selection.Copy
    Application.ScreenUpdating = True
End Sub

and this in the personal.xls that you can store "always available" macros in

 
Sub PasteasText()
'
ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= _
False
Selection.Copy
End Sub

[Dices]

Thanks for the replies, for the moment i'm trying some ideas of my own trying to figure it out using only formulas and not macros.
If i find anything i will let you know.
Thanks again.

[georgej]

Sorry Dices - didn't notice i replied to only part of your query.

To retain the formatting it is the copy, pastespecial etc in word twice, then excel
To convert from fraction odds to decimal, in the next cell i use a find "/" formula. I'll look it out now.

oops - wrong formula - that was for position - only gave figure before the "/"
=MID(H13,1,FIND("/",H13)-1)*1/RIGHT(H13,LEN(H13)-LEN(MID(H13,1,FIND("/",H13))))*1+1
with H13 being 7/1

[Dices]

Converting fractional to demical:

=LEFT(D5;SEARCH("/";D5)-1)/RIGHT(D5;SEARCH("/";D5)-1)+1

edit:
it's corect if it converts two sincle numbers ie 5/4 but not a 33/1 ??????

EDIT: totaly wrong formula

[georgej]

It seems to be assuming that the length is the same on both sides.

You will need to take into account the length of the original string.

Edit: Never used search before in a formula, so good to see it can cut it down a bit.

[georgej]

What's wrong with the formula? You were missing only one last small piece. Less than 6 characters!!!

Was going to give you the satisfaction of completing it - i think it's a pretty nifty formula myself.

[Dices]

I''ll find it , it will only take me time.
This is why i like excel : " the joy of creation "

Thanks

[Dices]

OK, here it is.

Column with SP odds from RPS (or elsewere) is copied at column B. It contains fractional odds 7/1, 5/4 etc ,allways a number like 3/1F (the fav) and sometimes a number like 4/1J (the join fav).
At the below table the two above letters (F & J) are removed.
At the below table the Column J (Year) is not required.
I think it will be helpfull to some (at least,I will use it)
Please, post your coments and any impovements.

A	B	C	D	E	F	G	H	I	J	K	L
2				No of Characters at Odds
3	Odds Fraction	DATE	Fav / Jfav & odds	No Chr at Column D	First No at Column D No of Chr	Second No at Column D No of Chr	DAY	MONTH	YEAR		Decimal ODDS
4	16-Jan	YES	DATE	DATE	DATE	DATE	16	1	2007		17,00
5	3-Jan	YES	DATE	DATE	DATE	DATE	3	1	2007		4,00
6	5/4F	NO	5/4	3	1	1	ODDS	ODDS	ODDS		2,25
7	33/1J	NO	33/1	4	2	1	ODDS	ODDS	ODDS		34,00
8	20-Jan	YES	DATE	DATE	DATE	DATE	20	1	2007		21,00
9	14-Jan	YES	DATE	DATE	DATE	DATE	14	1	2007		15,00
10	100/1	NO	100/1	5	3	1	ODDS	ODDS	ODDS		101,00
11	7-Feb	YES	DATE	DATE	DATE	DATE	7	2	2007		4,50
12	100/1	NO	100/1	5	3	1	ODDS	ODDS	ODDS		101,00
13	100/1	NO	100/1	5	3	1	ODDS	ODDS	ODDS		101,00
	FORMULAS (copy down from row 4 to row 13)
	C4=IF(TYPE(B4)=1;"YES";"NO")
	D4=IF(TYPE(FIND("F";B4))=1;LEFT(B4;SEARCH("F";B4)-1);IF(TYPE(FIND("J";B4))=1;LEFT