Stop Excel from converting football scores into dates

hello all
i have his problem
http://www.excelforum.com/excel-gene...cognition.html
and im copying and pasting data from a website ( football scores )
and when i get what should be 1-1 it returns 01-jan and this i dont want
i have tried formatting all cells to text beforehand but that makes no difference and i cant put an apostrophe before each one as that would take ages
wondered if anyone could work out some syntax to use as a macro button?
claymation had a go but it doesnt work.

thanks

EXCEL, by default, has the style as "Normal". ie during cut-and-paste it tries to interprete the copied value to its known formats of date and number and if it finds a match it takes it as date or number. Only if does not match to its known format it takes it as text. Your score of 1-1 it assumes as date ( 01-Jan ). To over-ride this, format the whole column in which you are pasting the score as " TEXT" ( select the whole column then format cell > number> category-text ) BEFORE you paste data on to the sheet.
If you think that you want data in all the columns to be taken as text then you can change the style of the workbook before you paste any data on it. ( Format>Style and then modify number format to text ). Style applies to the whole workbook ).

A V Veerkar

Hi thanks for the reply avveerkar

i have done it exactly as you said previously as per my first post
and it doesnt work

Originally Posted by excellentexcel

Hi thanks for the reply avveerkar

i have done it exactly as you said previously as per my first post
and it doesnt work

I am sorry that I did not read your post carefully. You seem to have already tried what I suggested. The method should work if you are manually selecting data from a table on the displayed web page and then just pasting it on your EXCEL sheet ( control C on the web page and then control-v on excel sheet ). You seem to have some other way of getting the data- web query?
Could you pl tell me how you are getting the data? Preferably give the steps with URL of the web page etc so that I can recreate what you are doing. I would really like to work on this problem. We need to do something so that the correct data is imported to excel. It is almost impossible to regenerate the original text data once EXCEL has applied its format conversion.

A V Veerkar

hi avveerkar

i have tried both ways
copy and paste exactly as you say and that DOESNT work

web query on the web page doesnt do any other seasons than the current one

one is for web query
the other is a copy/paste/format issue

says you should ask different questions in different threads

Originally Posted by excellentexcel

hi avveerkar

i have tried both ways
copy and paste exactly as you say and that DOESNT work

web query on the web page doesnt do any other seasons than the current one

OK I see the problem. Before we get a proper elegant solution, as a make-shift arrangement, you could format the whole column in which you have pasted the scores, as "d-m", after you paste the data. ( select column and then Format>Cells>Number>Custom and then enter type as d-d ). This will give correct display but keep it in mind that excel is taking it as date and not a text string which is what ideally it should have been.

A V Veerkar

Originally Posted by excellentexcel

how about if cell has Jan in the box that equals
so 1-1 would be 01-jan which would return 1-1?

same for all the other months?

basically avverkar or anyone else
excel doesnt understand your way of formatting as it doesnt understand where and second number is greater than the first

Sorry I meant to say format as d-m and not d-d. It was my typogrphic error.

hey avveerkar

thats good enough for me as i will just get the macro to do it for me

Please Login or Register  to view this content.

thanks for pointing me the right way

Dear Excellentexcel,

Happy to know that it worked for you.

A V Veerkar