Excel formatting help

[patswin]

quick question, hopefully an easy one. i want to scrape data from a web site. I am doing it though excel web query function on excel 2007. When i get the data, the number formats change. For example, in one cell the data is 5-3, but when it goes into excel it automatically formats into a date 5/3/2011. how can i get it to keep it 5-3? I tried reformatting the cell but nothing seems to work. Can someone explain how to get it to work

Thanks

[thompsontw]

Select all your data cells that you want to change the format on then Go into the Format Cells window and the Number tab then Custom Category then type m-d in the Type box.

[patswin]

ok that worked but now when i update the spreadsheet it still changes it to date format. anyway to lock the cell into the m-d format?

[WendysRox]

I'm sure there are better programmers out there than can tell you how to do that. But, the way I would do it with my limited knowledge is to record a macro of you changing the formats, then after you scrape the data all you have to do is run that macro.

I do the same thing to get rid of periods that show up when I'm scraping (a website I use puts periods when the value is null).

[mrtomk]

I'm reading this as 5-3 is a team's record or something and not in any way a date? I think its a matter of formatting the cells/sheet in question, prior to importing the data, to Text, following the steps outlined by the previous poster.

If you import the 5-3 prior to formatting the cells, Excel with convert it to a date, or number based on the date, in this case 40666.

[Pushkin]

In the Web Query, after you have selected the data that you are going to import into your spreadsheet (but before you click the import button), click on the options menu in the Web Query and than disable the date recognition. Now it'll import into your spreadsheet properly.

[Insoluble]

This should do the trick for you:

http://support.microsoft.com/kb/287027

[MonkeyF0cker]

Assuming that you're doing it programmatically and creating a range of cells for importing the data, it's simply the following command to format the cells in that range to text...

Worksheets("SheetName").Range("Cell1:Cel l2").NumberFormat = "@"

Otherwise, the previous post would alleviate that.

[Jenney001]

learn more from reply..

[patswin]

thanks guys for your help! got it to work ok now

[arwar]

i add a string like --> before the data (that gets unwillingly converted to date like 6-10 for height - i use '--> 6-10' -no quotes - though you can use quotes as well) anyway after loading then delete the '-->'

[ScreaminPain]

One solution is this:

right-click on your spreadsheet
click on Edit Query
in small print at the very top right is "options" ...click on it
when the new box opens find the phrase "disable date recognition" and check the box next to it.
close
close
close

Your problems will be solved..