In this example I’m blaming excel for assuming my pasted data has ‘accidentally’ acquired a leading zero which it assumes I don’t want. It shouldn’t be assuming anything! I’m ok with it detecting the type of the data. I’m not ok with it changing the actual data
Perhaps you should put more effort into learning the software than complaining about it.
I am quite happy to learn things. I take issue with things being more complicated than they need to be though. I have a Software Engineering Degree. I know that Useability is supposed to be very important. I find that particularly Microsoft’s Software seems to ignore useability.
And I can still complain about something while at the same time learning it. Did I not mention earlier in this thread that I was or will be able to figure it out? I did. so please don’t acuse me of being lazy and moaning. If you don’t like this type of thing why the fuck are you reading threads in this subforum? Is it so you can be a jerk?
Well, when you post threads like this, it’s hard to tell whether you’re asking for advice or just kvetching. If you’re requesting input from other folks, it would be helpful if you’d be clearer about what you’re trying to accomplish and what specifically you’ve tried so far.
For example, after poking around for about 30 seconds it appears that Excel will happily open a tab-delimited text file that contains columns with leading zeros or slashes; I just have to remember to format the columns as text during that process. However, you don’t seem to have clearly stated whether opening files manually is acceptable, or if you’re trying to automate the process, or whether it’s acceptable to treat serial numbers as text; etc.
I appreciated the help I got. But I had hoped the fact that I’d put it in the Pit would make it relatively clear to people that I was venting. It isn’t my fault it was moved to MPSIMS. It has the ‘Pit’ Label attached. Meaning that it was mainly a vent.
And the data is being copied directly from sql management studio (less steps involved than creating a csv file and opening the csv file in excel - or I would like to have thought so) into excel.
I took offence to yoyodyne because I think disdain for bad design is legitimate.
I was trying to learn Excel 2.1 (it was nice when MS included the decimal so you knew to never touch Program X.0) macros coming from 123. The manual had examples but many of them used commas for some critical reason I forget as I stopped using macros in Excel at about that time nearly 20 years ago. Had the manual used a monospaced font I could keep adding commas until it lined up with the line in the book above it, but they used a proportional typeface so I could either try to count the itty bitty 4pt commas (if I still had my manual I could show you the ticks I added at what I hoped were 5-comma intervals) or just keep adding them until the macro ran.
I’ve said it before, but the cleanest spreadsheet data I ever tried to plug into any SQL DBMS came out of the first DOS version of Quattro.
I have no real complaints about Excel. Does the job for me. I’ve used spreadsheets from VisiCalc to SuperCalc to 1-2-3 to Quattro. Excel’s the best of the bunch, especially when you include all the add-ins. When Excel doesn’t do what what I had expected it to do, I’d figure out why and learn to do it right.
I was (finally) able to get around the leading zeroes on serial numbers thing by converting the affected cells to text cells. Fortunately, I was still able to sort the columns into numerical order after I did that. Why is there no setting for numbers with leading zeroes? Why? Why? Why? Especially since I am apparently not the only person who has had this problem? GRRR.
Just what do you expect it to do? if you multiply 03 by 0002 do you want it to be 6, 0006, 06, or 000006? There is no mathematical rule for arithmetic with leading numbers. If you aren’t doing arithmetic then format it as text. I mean really, if you enter “123” in a column do you want it treat it as a number? What if you put “abc” in the next row, should it go back and treat the first cell as text?
So with 2 minutes work I figured out that specifying a custom format of “000000000” displays a 9 digit serial number with leading zeros.
Can’t you select the cells, select format, choose “Custom” as the category, and then just type in the number of zeros your numbers are supposed to have. For example, if your numbers are all supposed to be 7 digits with leading zeros, just type in 7 zeros.
I use this all the time in Excel 2003, I’m pretty sure I’ve seen the same option in the 2007 version.
You remind me of a user I knew who once had to call the helpdesk frantically because she accidentally increased the size of a DOS window. She couldn’t work because it was different. And whens he accidentally moved the taskbar to the side of the screen, she was completely lost.
Excel 2007 is different – because they’ve put things in more logical places and made the most common commands easier to find. Maybe you don’t like things to be easier to use; you’d rather stick with some half-assed method of doing things because you’re used to it. But computers don’t work that way and all the complaining in the world isn’t going to change that fact.
I didn’t need to do mathematical equations with the serial numbers, just type them into a list and sort them into numerical order. A customer requested that a list of serial numbers be sent with their parts, which is kind of an unusual request for our company. Therefore, I had to come up with a way to do it. Haven’t used Excel much, but I did take a class.
I don’t remember seeing a “Custom” format, or else I would have tried it. I tried everything else. I’m eager to look tomorrow and see if I could do it that way if it ever comes up again. I can’t remember which Excel version we have. I know it’s not 2007. Might be 2003, but it wouldn’t surprise me much if it was older than that.
Well in that case you just format the column as text, do a “paste special”, and select “contents” under “Paste” on the paste special form.
What I’m gonna go around and deliberately change the keys on a piano. I won’t do much. I’ll just decrease the size by a few millimeters. I bet people who don’t really play are going to enjoy being able to reach a little bit further. Yeah, that concert pianist who knows the piano so well he plays by instinct and muscle memory? Yeah, it’ll throw him off, but who the fuck cares about him? He should embrace the fact that the new piano keys are easier to play.