JSngry Posted June 16, 2011 Report Share Posted June 16, 2011 Please, people, PLEASE. If you're a casual spreadsheet user but share the ones you create with people who actually use them as something other than a word processor with cells, please learn how to standardize the format of what you put in those cells. If you know who this is talking about but not what, odds are good that you need to ask some questions. But not to me. Ask the people you to whom you send the data. And if they don't know, ask them to ask. At some point, somebody's probably getting very pissed off having to clean up after what they might consider y'all's ignunt asses. Thank you for your consideration, and Drive Friendly! Quote Link to comment Share on other sites More sharing options...
Bill Nelson Posted June 17, 2011 Report Share Posted June 17, 2011 The next offender goes to the Penalty Box for 30 days. Quote Link to comment Share on other sites More sharing options...
BFrank Posted June 17, 2011 Report Share Posted June 17, 2011 What's THIS all about? [just a curious Excel user] Quote Link to comment Share on other sites More sharing options...
Kevin Bresnahan Posted June 17, 2011 Report Share Posted June 17, 2011 Huh? What happened? Formatting a spreadsheet a different way takes two or three clicks, tops. Quote Link to comment Share on other sites More sharing options...
Chicago Expat Posted June 17, 2011 Report Share Posted June 17, 2011 The next offender goes to the Penalty Box for 30 days. And he was, at least, nice enough to say, "Please, people, PLEASE." We never have these problems on Lotus. Quote Link to comment Share on other sites More sharing options...
.:.impossible Posted June 17, 2011 Report Share Posted June 17, 2011 I'm with Kevin. What are you trying to do Jim? Maybe we can help. Quote Link to comment Share on other sites More sharing options...
JSngry Posted June 17, 2011 Author Report Share Posted June 17, 2011 Huh? What happened? Formatting a spreadsheet a different way takes two or three clicks, tops. Reformatting a whole spreadsheet, yeah. Easy. But the data needs to be consistent throughout each cell. No extraneous spaces before and/or after values, names all in the same format, consistent usages of commas,dots,slashes in dates and phone #s, names either first, last or first last all the way thorough, please don't put some full names in one cell, others divided up between cells, please take the hyperlinks out of the email addresses, stuff like that. It's all fixable, but it's also preventable on the front. The work on the back end to fix it grows exponentially as various inputs need to be combined into one output. Get it right the first time, please. Please? Quote Link to comment Share on other sites More sharing options...
JSngry Posted June 17, 2011 Author Report Share Posted June 17, 2011 I'm with Kevin. What are you trying to do Jim? Maybe we can help. Text-to-columns should never have to be used as a mere clean-up tool. Same with the Upper, Lower, and Proper functions. The Left & Right functions should return proper values first time out. Find an replace should be an editing tool, not a way to get dots where slashes should be and vice-versa. Concatenate is a gift from god, but only if what you're concatenating is consistent and clean. Then again, all that stuff is in there for a reason, they know how people gonna do!. I just think that people should be more conscious, to know that when they're asked to provide data, it doesn't mean to promiscuously run your mouse over whatever you have, then gleefully paste it all into a spreadsheet, and then think you're done with it, pass it on, game over, that was only took five seconds, EASY! Take 15 or 20 peopl doing that with data that's supposed to end up as one, consistent document, and you might as well get ready to clean up a New Orleans whorehouse on New Year's morning... It's kinda like sending session tapes to the pressing plant and expecting them to edit and master them there. No, do that first. Then again, when you're one of a handful (it seems) out of a thousand or so who knows how to do this stuff, hey - job security. Because most folks are lazy and/or scared and don't want to be bothered to learn that yes, concatenate is a real word! Quote Link to comment Share on other sites More sharing options...
Dan Gould Posted June 17, 2011 Report Share Posted June 17, 2011 Sounds like Jim is the guy at the bottom of the funnel, and required to submit a single consistently formatted spreadsheet. Quote Link to comment Share on other sites More sharing options...
JSngry Posted June 17, 2011 Author Report Share Posted June 17, 2011 That's about it, although a lot of what I do involves shipping out pieces of my master spreadsheet to various access departments so they can run a script to do their thing. A field or two with an errant space in it throws their program off, and then they gotta clean it up. So I like to make sure it right going in. I don't want to be "that guy", the one who always sends in the jacked-up data. I think more people should try to not be "that guy", no matter what the context. "That guy" is not somebody good to be! In corporate jargon, I've become known as a "gatekeeper", but in all honesty, I prefer to think of myself as a greeter, The Greeter to the Clean Data Club. Quote Link to comment Share on other sites More sharing options...
Dan Gould Posted June 17, 2011 Report Share Posted June 17, 2011 (edited) You're making me a little verklempt though. Edited June 17, 2011 by Dan Gould Quote Link to comment Share on other sites More sharing options...
alankin Posted June 17, 2011 Report Share Posted June 17, 2011 6.00 6 $6 60% 6.0000009 £3.7108 €4.1996 A CrY 4 H3!p. Quote Link to comment Share on other sites More sharing options...
mjzee Posted June 17, 2011 Report Share Posted June 17, 2011 I promise not to do it again. Quote Link to comment Share on other sites More sharing options...
Christiern Posted June 17, 2011 Report Share Posted June 17, 2011 What the hell is Jim smoking? German bean sprouts? Quote Link to comment Share on other sites More sharing options...
JSngry Posted June 17, 2011 Author Report Share Posted June 17, 2011 Ok, here's one that's a little more variable, but still a pain. If you're putting the whole name into one cell, be consistent with whether or not a middle initial is used. Because when you use a space delimiter, you end up with all the first names in one column, most of the last names in the next column, but a few middle initials in that second column, with those last names being in a third column all by their lonesomes, Which results in the need for further cleanup. If you're asked to provide this type data, ask the person who's ultimately going to work it how they'd like it presented, but for me, if a middle initial is essential for an individual, like if it's just gotta be Roberto Q. Lewis, then your safest bet is to put first & last names in separate cells. Then it's easy to concatenate the two cells and get a single full name in a single cell, no problem with a middle initial being in there or not.. Same thing with "nicknames", like if it's "Robert (Bob) Q. Lewis" Put all that other auxiliary stuff in the same cell as the first name, or else just make it Bob Lewis and let it go at that. I'm not the only person in the world who has to deal with this, believe me. I'm not even a "tech guy", I just fell into the gig and grew with it as I went. As a result I've picked up a lot of pointers from the people who are truly to this manner born, and trust me, it matters. But like most natural geeks, they keep their complaints to themselves, because they figure they would fall on deaf ears. But I believe that the good people of this world WANT to do the right thing by their coworkers, and that is why I bring my plea here, to this forum, where some of the most influentially conscientious citizenry of the free world reside. Together, we CAN make a difference! Quote Link to comment Share on other sites More sharing options...
.:.impossible Posted June 17, 2011 Report Share Posted June 17, 2011 Ok, but there is no telling where the data is coming from. You are assuming that all of this data was collected for one specific purpose. Here's a start: http://excelhints.com/2009/01/24/separate-full-name-into-first-last-name/ Quote Link to comment Share on other sites More sharing options...
JSngry Posted June 17, 2011 Author Report Share Posted June 17, 2011 (edited) Oh, but I do know where the data is coming from! And they send it specifically to me, because I'm the one guy that does something with it for them. It's coming form a disparate group of lower managers & upper-level frontline people who are copying & pasting off of intranet websites and/or spreadsheets that they complied themselves from copying out of word docs & emails & already sloppy spreadsheets. That's how you get data with spaces at the beginning and at the end. And live email hyperlinks. I hate a bigass column of live email addresses, it's like walking through a minefield, one stray twitch of the finger and BOOM you're looking a new letter that you don't want/need to sent to somebody you probably don't want/need to know. GRRRRR!!!!!! That's the first thing I do, remove all those hyperlinks, and the happiest day of my recent life was when I found out how to do them all at once. What that says about my recent life is pretty obvious, but I'm jsut sayin', it was a good day. Your link is interesting. That's not the way I do it, I use Text-To-Columns (we use Office '07, and the feature is significantly more flexible than it was in previous versions, as is Excel in general), but the LEN formula is one with which I was not familiar, so hey, something learned, always a good thing. Thanks! Edited June 17, 2011 by JSngry Quote Link to comment Share on other sites More sharing options...
J.A.W. Posted June 17, 2011 Report Share Posted June 17, 2011 (edited) Off-topic: why are "data" and "media" always seen as singular? They are plural, the singular being "datum" and "medium" respectively. And while we're at it, another error that is often made is "ad nauseum", the correct spelling is "ad nauseam". Edited June 17, 2011 by J.A.W. Quote Link to comment Share on other sites More sharing options...
Matthew Posted June 17, 2011 Report Share Posted June 17, 2011 Off-topic: why are "data" and "media" always seen as singular? They are plural, the singular being "datum" and "medium" respectively. And while we're at it, another error that is often made is "ad nauseum", the correct spelling is "ad nauseam". Because Americans love to drive non-native and non-American English speakers nuts over how we use and spell the language. Hey, it helps to pass the day for us. Quote Link to comment Share on other sites More sharing options...
JSngry Posted June 17, 2011 Author Report Share Posted June 17, 2011 I guess you could make the argument that anything other than a single-character is "plural" in nature, i.e. - "16" = two characters, and therefore in the context of a spreadsheet could be classified as the data in a cell. I guess... Besides, "datum" is subliminally suggestive of "rectum", which is what so much data ends up being used to assault, so we probably postpone the inevitable as much as possible through linguistic aversion. Quote Link to comment Share on other sites More sharing options...
Dave Garrett Posted June 18, 2011 Report Share Posted June 18, 2011 The most essential toolkit I've ever run across when it comes to having to deal with fucked-up data formatting in spreadsheets: ASAP Utilities Quote Link to comment Share on other sites More sharing options...
Jazzmoose Posted June 18, 2011 Report Share Posted June 18, 2011 Off-topic: why are "data" and "media" always seen as singular? They are plural, the singular being "datum" and "medium" respectively. And while we're at it, another error that is often made is "ad nauseum", the correct spelling is "ad nauseam". Because Americans love to drive non-native and non-American English speakers nuts over how we use and spell the language. Hey, it helps to pass the day for us. Yeah, it's kind of a hobbye for us. Quote Link to comment Share on other sites More sharing options...
Chuck Nessa Posted June 18, 2011 Report Share Posted June 18, 2011 Quote Link to comment Share on other sites More sharing options...
JSngry Posted June 18, 2011 Author Report Share Posted June 18, 2011 The most essential toolkit I've ever run across when it comes to having to deal with fucked-up data formatting in spreadsheets: ASAP Utilities Looks really cool...don't know if I can install it on the corporate PC, but if I can... Quote Link to comment Share on other sites More sharing options...
Dave Garrett Posted June 18, 2011 Report Share Posted June 18, 2011 The most essential toolkit I've ever run across when it comes to having to deal with fucked-up data formatting in spreadsheets: ASAP Utilities Looks really cool...don't know if I can install it on the corporate PC, but if I can... It's been a while since I've had to install it on a machine that I didn't have local admin access on, so I can't remember if that's required or not. It's basically a collection of Excel macros, and IT organizations tend to get cranky about things like that when they're not coming from a trusted source. Also, be aware that the developer publishes updates semi-regularly, and will force you to upgrade after a certain period of time by disabling the older version you have installed. Not sure if this is done by just embedding an expiration date in it or whether it phones home to check for available updates. Kind of a pain in the ass, especially if it turns out that admin access is required to install the updates, but it's so useful that I've managed to overlook these kinds of minor hassles. Quote Link to comment Share on other sites More sharing options...
_forumlogo.png.a607ef20a6e0c299ab2aa6443aa1f32e.png)
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.