Warning. Swearing.
It’s taking leading zeros off serial numbers.
Turning datetimes into times.
Assuming commas and slashes mean “this here is a date. I’ll format it as date” (and being wrong)
putting e+ or something on long numbers.
Excel, please just leave it the
fuck
alone!
Dare I assume that you know that putting a ’ before your data makes Excel put it in the cell exactly as you typed it?
I’m glad I’m not the only one who gets frustrated and types cuss words into help dialogue boxes.
I know you didn’t ask for help, but can I assume, if everything in each column is to be formatted (or not formatted) the same way, you’ve already selected the entire column and changed the formatting? If you do that, it should remember and not try to predict your formatting preference for that column anymore…
Or maybe not, it’s been a while since I’ve used Excel.
The requirement to do that seriously hinders the sql query one needs to write in order to get the data in the first place.
ETA: What does excel ‘do’ with the apostrophe once it’s imported? Are numbers still numbers? (can they be included in excel sums?)
Well the really frustrating aspect of it is that you don’t get to do that until after you’ve pasted and by that time the damage is done. So if I change the ‘dates’ back to numbers or ‘text’ they become different numbers (the ‘behind the scenes’ version of the ‘date’)
I can usually manage to get it sorted. It’s just incredibly frustrating that what is supposed to be a useable application requires this much fucking with to do what it should be designed to do by default: (i.e. assume the person using it knows what they are doing and wants their data to remain as it is. and if they do want a change, let them make the change)
Hmm, working with sql query and Excel re-formatting the data coming in - I have a strong feeling there is a way of fixing this, but it’s been a long time since I worked with databases. Oh, we were working with Access, not Excel - still, I think there may be help for you, Lobsang. Have you or your computer gurus tried to fix this? I think with sql you can dump the data raw, and just format after it’s dumped.
I should say, normally I wouldn’t be getting in the way of someone’s righteous rage at a computer program, but my respect for Excel is pretty high.
Are they holding your children hostage or something?
[sub]Joke :)[/sub]
I appreciate being given help. I think the rage has subsided 
Click the box in the upper left corner of the border to highlight the entire spreadsheet and then format for numbers, whether to use a comma separator format like 000,000 etc. There may be a way to leave leading zeros too, I don’t have Excel on this machine or I would look into it.
Then if you actually want a date to display in a particular box you can still format that box individually.
But I DO agree. There should be a large button at the top of most Windows software that says “Do Not Think For Me!” I’ll ask when I want help.
The problem is. If I try to anticipate it (format all for text then paste) it still happens exactly the same way.
And if I try to do the format after pasting, the damage is already done. It’s too late.
I use Excel for all sorts of work, and am glad it’s pretty versatile for those purposes.
But I often, oftenoftenoftenoftenoftenoftenoftenoftenoftenoften, use it for general calculator-like things. A column of numbers here, another there, a little interaction between the two and maybe a basic if>then statement or two. No big deal.
Is there any way … ANY WAY … I can get Excel to automatically treat numbers as they are typically formatted in most uses?
If I have more than four digits, put a comma in (or a period if you’re over there).
If I type (or a calculation generates) a decimal, put it in. Else, you know, don’t.
Isn’t that simple? Isn’t that how most everyone uses numbers?
I know I can do it manually, but that’s kind of, well, not automated. Plus, if I set it for the whole sheet, I lose any other formatting I’ve put in place.
I may be able to edit the normal template and get commas in there, but then I have to choose (and stick with) a number of decimal places.
I basically want the default cell formatting to be people-default.
Any chance?
Tell me there’s a new option in Excel 2007.
Please.
The apostrophe makes it text, so it can’t be summed.
If this is a common task, where the query will always return the same data in the same columns, I’d create a template with the columns pre-formatted.
In excel 2007 they’ve carefully noted where everything was in the previous version, and then carefully put each individual thing in the most different place possible from where it was in the previous version.
The effect they wanted to achieve was users opening excel up and then just staring… and staring… and eventually developing the thousand-yard stare.
ETA: Which reminds me about something that infuriates me about supermarkets and the geniuses that run them - Just when you’ve finally managed to find out which bizarre location has been chosen for a particular product to be shelved and then committed that location to memory, the super-genius ‘general manager’ decides it would be a very managerially-official-like-look-at-me-I’m-being-managery thing to randomly re-arrange the entire store for no apparent good reason.
I’m sorry, but if I had to tell Excel every single time what format to use I would go nuts.
Moved from The BBQ Pit to Mundane Pointless Stuff I Must Share.
Gfactor
Pit Moderator
The commas you should be able to do by choosing a number format. If I’m understanding you correctly, though, what you are trying to do with decimals will bother a lot of people. There is a difference between addition and subtraction of whole numbers, which understandably won’t reflect a decimal, and division or percentages which happens to yield a number that includes only a zero after the decimal point. Your number format should reflect the type of calculation you’re doing and a consistent number of decimal places. For example, if you are providing turnover rates of 15.1%, 16.2%, and 17%, that looks odd.
I work with Excel every day. Sometimes you have to sweet talk it. It’s the only way it learns.
My favorite pet phrase is, “Come on, you fucking whore!”
My boss always knows when I’m busy when he hears that wafting over the cube walls.
If the serial numbers are the same length then you can format it to display correctly. However, don’t blame Excel for assuming a number is a number and not text. It HAS to make an assumption or nothing would happen. Since it’s mainly a number crunching program the default for treating numbers should be as it is.
I feel for you because I’ve run into more problems downloading data than I care to talk about but that’s the nature of the beast.
I used to throw things when I had a private office. Had to keep soft objects on the desk.
Aight, checkit…
Put your date into A1, let’s say. In B1, type =Text(a1, mm/dd/yy) or some other date format (yyyy perhaps). Then copy column B and paste overtop of it, selecting Paste Special.
You’re done.
CS
Powerpoint Ranger
Excel Gangsta
I’m not sure how you are querying your database, but I typically have good luck by taking comma delimited results and pasting them into Excel. Once you paste there should be a little “Paste Options” pop-up with a “Use Text Import Wizard” option. After selecting the options that make sense for comma delimited data you get to choose how to format each column. Selecting “Text” for a column should mean that it is left as-is.
That has worked for me. It may not be the most efficient way to do it - I am by no means a power user of Excel!