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

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

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)


"ahughf" wrote in message
...
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

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

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!

"Peo Sjoblom" wrote:

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)


"ahughf" wrote in message
...
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

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

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')




ahughf Wrote:
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!

"Peo Sjoblom" wrote:

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)


"ahughf" wrote in message
...
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




--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=466522