How do I stop Excel auto formatting the text 3-4 as 04 Apr?

[Guest]

I run a web query that imports soccer results to Excel. Unfortunately the web
site formats the result as 3-1 for example. This text is automatically
converted to 03 Jan by Excel. I can't seem to stop this happening, even if I
explicitly set the cell format to text. Doing so just converts the contents,
which were a date, to the internal date number and if I re load the data it
reverts to 03 Jan again. Does anyone know how I might get excel to leave the
format of the cells alone?

thanks ahughf

 

[Peo Sjoblom]

There is no turn off for this, you can either preformat the cells as text or
precede the entry with an apostrophe '

--
Regards,

Peo Sjoblom

(No private emails please)

 

[Guest]

Unfortunately I cant alter the data on the way in as it comes from a web
query from a site I have no control over. Also preformatting the destinations
cells as text makes no difference, good old excel knows better and just turns
them back to date as the data comes in.

thanks anyway, it has forced me to brush up my VBA skills as I couldn't
stand seeing Manchester united beeting west brom by the 3rd of January any
longer!

 

[Bryan Hessey]

Does this indicate that you are inputting as a .csv (etc) file?

If so, rename the file to .txt, and in the input process use Separated
by = Comma (etc) and highlight the selected column in the Column Data
Format screen (thhird in the wizard) and tick this to be TEXT.

This will maintain the format as 3-2 and not connvert it to 2nd March

(note, I am unfamiliar with 'web query input')