So I've set up a data connection to a table on my website and the data imports just fine, but it formats the cells wrong.
Case in point:
The text coming in is "May 7". First Excel auto-converts this to "5/7/2011". But then it doesn't even display that, it displays "7-May".
What's even worse, if I change the cell formatting to "text" the date now becomes "40670". Basically, Excel refuses to display the ACTUAL freakin data I'm telling it to import! How frustrating is that?
I'm just flabbergasted. Not only does Excel never show the ACTUAL text I'm importing, it converts it in THREE different ways! WTH? They think this is helpful?
Anyway, every time I import the table, the formatting changes, so it doesn't STAY as text. And yes I know I can put a single quote and type " 'May 7 " and it will stay, but this needs to be automatic. I'm not going to spend 10 minutes re-typing every cell that displays wrong, and it all goes away when I refresh the data anyway.
So the question is, how can I force the cells to be text only, and not reset when data is imported? Or basically, get Excel to show me the real data I want, and not it's dumb conversions. It has to be a solution that is unique to the one file, not a global change, since other people use this file, the settings have to be in the file.