# Is there a way to make Excel less annoying (.csv files)? **URL:** \ **Category:** Factual Questions\ **Created:** [April 9, 2014, 5:46pm UTC](https://boards.straightdope.com/t/is-there-a-way-to-make-excel-less-annoying-csv-files/685738 "2014-04-09T17:46:54Z")\ **Posts on this page:** 16\ **Page:** 2
**Author:** ![RitterSport](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/rittersport/32/6326_2.png) [@RitterSport](https://boards.straightdope.com/u/RitterSport)\ **Post date:** [April 10, 2014, 3:26pm UTC](https://boards.straightdope.com/t/is-there-a-way-to-make-excel-less-annoying-csv-files/685738/21 "2014-04-10T15:26:25Z")
> [@Johnny\_L.A](#): > > Sadly, I’m the one with the most knowledge of Excel in the office. My boss and coworker think I’m some sort of genius because I showed them how to get leading zeros on ZIP codes, and because I do magical things like using =CONCATENATE. But I’ve given up trying to make VLOOKUP work (I’ve made it work in one file, and then using the exact same code (with proper columns, of course) it fails in another file), and anything involving macros or Excel programming is beyond me. A large part of my job is to put Excel and other files into specific, fixed-position, text files. It’s easier for me to write an Easytrieve to do what needs to be done. (Easytrieve is a sequential program from, like, the '60s or '70s that bears a passing resemblance to COBOL or BASIC.) > > So when I come upon something I need to do that I don’t know how to do. I turn to you guys. If it involves macros or Excel programming, I think I’ll just have to live with clicking 27 times to save and close a .csv file. Ah, gotcha. ---
**Author:** ![PoorYorick](https://avatars.discourse-cdn.com/v4/letter/p/ecccb3/32.png) [@PoorYorick](https://boards.straightdope.com/u/PoorYorick)\ **Post date:** [April 10, 2014, 6:21pm UTC](https://boards.straightdope.com/t/is-there-a-way-to-make-excel-less-annoying-csv-files/685738/22 "2014-04-10T18:21:14Z")
> [@Johnny\_L.A](#): > > Sadly, I’m the one with the most knowledge of Excel in the office. My boss and coworker think I’m some sort of genius because I showed them how to get leading zeros on ZIP codes, and because I do magical things like using =CONCATENATE. Yeah, for about 10 years I was the Excel guru in my office. Then I changed jobs where _everyone_ knows how to make Access and Excel do everything but make coffee. I’ve learned more in 2 years than I have in the 10 years before that. It’s such a nice luxury to have someone to confer with when you hit a snag, rather than slogging through Google. ---
**Author:** ![KneadToKnow](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/kneadtoknow/32/3999_2.png) [@KneadToKnow](https://boards.straightdope.com/u/KneadToKnow)\ **Post date:** [April 10, 2014, 6:25pm UTC](https://boards.straightdope.com/t/is-there-a-way-to-make-excel-less-annoying-csv-files/685738/23 "2014-04-10T18:25:59Z")
Lotus 1-2-3’s macro language had a @BREW function. I miss it. Best coffee ever. ---
**Author:** ![Don\_t\_Call\_Me\_Shirley](https://avatars.discourse-cdn.com/v4/letter/d/f4b2a3/32.png) [@Don\_t\_Call\_Me\_Shirley](https://boards.straightdope.com/u/Don_t_Call_Me_Shirley)\ **Post date:** [April 10, 2014, 7:39pm UTC](https://boards.straightdope.com/t/is-there-a-way-to-make-excel-less-annoying-csv-files/685738/24 "2014-04-10T19:39:29Z")
> [@Johnny\_L.A](#): > > Sadly, I’m the one with the most knowledge of Excel in the office. My boss and coworker think I’m some sort of genius because I showed them how to get leading zeros on ZIP codes, and because I do magical things like using =CONCATENATE. But I’ve given up trying to make VLOOKUP work (I’ve made it work in one file, and then using the exact same code (with proper columns, of course) it fails in another file), and anything involving macros or Excel programming is beyond me. A large part of my job is to put Excel and other files into specific, fixed-position, text files. It’s easier for me to write an Easytrieve to do what needs to be done. (Easytrieve is a sequential program from, like, the '60s or '70s that bears a passing resemblance to COBOL or BASIC.) > > So when I come upon something I need to do that I don’t know how to do. I turn to you guys. If it involves macros or Excel programming, I think I’ll just have to live with clicking 27 times to save and close a .csv file. VLookup sucks. Don’t use it. The columns in the table you’re pulling from have to be in the right order, it’s slow, etc… Try a combination of INDEX and MATCH instead. Like this: =INDEX({column you want to pull data from},MATCH({value you want to look up and match},{array where your match will be found}, 0) This way, the columns in the table you’re pulling from can be arranged in any order. I’ve also found it to be faster than VLOOKUP. So, if you’re on Sheet1, and you want to pull data from column A in Sheet3 based on a match in column E in Sheet1 and column C in Sheet3: =INDEX(Sheet2!A:A,MATCH(E1,Sheet3!C:C,0) should do it. The “0” gets you an exact match, and I’ve never had the occasion to use 1 or -1 there. ---
**Author:** ![Don\_t\_Call\_Me\_Shirley](https://avatars.discourse-cdn.com/v4/letter/d/f4b2a3/32.png) [@Don\_t\_Call\_Me\_Shirley](https://boards.straightdope.com/u/Don_t_Call_Me_Shirley)\ **Post date:** [April 10, 2014, 7:49pm UTC](https://boards.straightdope.com/t/is-there-a-way-to-make-excel-less-annoying-csv-files/685738/25 "2014-04-10T19:49:44Z")
Actually, that formula should be: =INDEX(Sheet3!A:A,MATCH(E1,Sheet3!C:C,0) I edited twice (because I originally had column D:D in the formula) and I still messed it up. That should give you an idea of how my regular Excel sessions usually go. :smack: ---
**Author:** ![John\_Mace](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/john_mace/32/185_2.png) [@John\_Mace](https://boards.straightdope.com/u/John_Mace)\ **Post date:** [April 10, 2014, 9:23pm UTC](https://boards.straightdope.com/t/is-there-a-way-to-make-excel-less-annoying-csv-files/685738/26 "2014-04-10T21:23:36Z")
Just had to thank the OP for a good laugh reading the title. It’s a lot better if you skip the parenthetical part at the end! 🙂 I use a lot of applications, but none is anywhere near as annoying as Excel. I must be designed to be so!! ---
**Author:** ![Learjeff](https://avatars.discourse-cdn.com/v4/letter/l/94ad74/32.png) [@Learjeff](https://boards.straightdope.com/u/Learjeff)\ **Post date:** [April 10, 2014, 9:37pm UTC](https://boards.straightdope.com/t/is-there-a-way-to-make-excel-less-annoying-csv-files/685738/27 "2014-04-10T21:37:23Z")
Yup, it’s annoying. To me the most annoying part is that after saving it in one format it asks to save again (before quitting) even though I’ve made NO changes. The only justification for this is “Well, it shouldn’t do that, but blah blah blah blah and it would be more work than you’d guess because blah blah blah.” Sad to say, I’ve been on both sides of that justification. It’s worse to be the one saying it than to be the one hearing it! But it’s a sad fact of life for huge programs that have grown by accretion over many years by thousands of programmers, rather than one designed by a nice tight team of clever people in a year or two. ---
**Author:** ![panache45](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/panache45/32/64_2.png) [@panache45](https://boards.straightdope.com/u/panache45)\ **Post date:** [April 10, 2014, 10:15pm UTC](https://boards.straightdope.com/t/is-there-a-way-to-make-excel-less-annoying-csv-files/685738/28 "2014-04-10T22:15:53Z")
> [@Learjeff](#): > > Sad to say, I’ve been on both sides of that justification. It’s worse to be the one saying it than to be the one hearing it! But it’s a sad fact of life for huge programs that have grown by accretion over many years by thousands of programmers, rather than one designed by a nice tight team of clever people in a year or two. If you want to make Excel less annoying . . . try Adobe Numbers. You’ll be begging for Excel. I’ve been using it for 18 months, thinking that in time I’ll get used to it . . . but I’m constantly finding more reasons to hate it. 18 months are long enough. I’m switching back to Excel. I guess Adobe’s team isn’t "tight or “clever” enough. ---
**Author:** ![Nava](https://avatars.discourse-cdn.com/v4/letter/n/da6949/32.png) [@Nava](https://boards.straightdope.com/u/Nava)\ **Post date:** [April 11, 2014, 7:05am UTC](https://boards.straightdope.com/t/is-there-a-way-to-make-excel-less-annoying-csv-files/685738/29 "2014-04-11T07:05:02Z")
> [@PoorYorick](#): > > A problem I have with Excel and CSV files is kind of the reverse. When I open a CSV file with Excel, Excel is sometimes too smart for it’s own good, and converts numerical text values to number format. I have large files where the Employee ID has leading zeroes (e.g. “003123456”), and Excel will strip those every time. Then I have to modify them to add the zeroes back in and convert it to text. > > Is there something simple I’m missing? Open it FROM inside Excel, instead of double clicking or using the right click menu “open with excel”. Popup saying “delimited fields”: next. Popup saying “coma delimited” (or tab or whatever): make sure the separator is the right one, next. In the list of fields, select all of them (you can move all the way to the end of the list and shif+click on the last one for this) and say they’re “text”. It’s one of the things I’ve had to tell people so many times in the last 12 years I wish I could have it macroed (the telling, not the doing). Right now I’m trying to badger a team of 12 programmers into doing it, but apparently they think it doesn’t apply to them; they keep losing the leading zeroes and the periods, and then the key users say “but it’s not the same!”. We have some key users who would much rather use full mechanical comparisons than the partial visual ones we’re having to do (at least one will do the mechanical and then just run through the screenshots), that requires the leading zeroes and periods and commas to stay exactly the same, so text format rather than general. ---
**Author:** ![BubbaDog](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/bubbadog/32/333_2.png) [@BubbaDog](https://boards.straightdope.com/u/BubbaDog)\ **Post date:** [April 11, 2014, 3:14pm UTC](https://boards.straightdope.com/t/is-there-a-way-to-make-excel-less-annoying-csv-files/685738/30 "2014-04-11T15:14:35Z")
**RitterSport** A possible macro with [Application.DisplayAlerts = False] was the first thing that popped into my head when I read the OP. That little statement has saved me many headaches when using Excel to manipulate other files. I like your code. I may use it in my “AlsoLoad.xlsm” file with one exception. It needs the line ’ \*\*\*\* Rittersport to the Rescue added somewhere near the top of the code. Bubbadog ---
**Author:** ![lazybratsche](https://avatars.discourse-cdn.com/v4/letter/l/ba8739/32.png) [@lazybratsche](https://boards.straightdope.com/u/lazybratsche)\ **Post date:** [April 11, 2014, 3:28pm UTC](https://boards.straightdope.com/t/is-there-a-way-to-make-excel-less-annoying-csv-files/685738/31 "2014-04-11T15:28:58Z")
Side question: Is there a decent alternative to Excel for making and editing simple CSV files? These days I’m often manually entering small-medium datasets into Excel, and saving as a CSV so I can do analysis with R. That means I’ve got to suffer the same constant-but-minor inconveniences described in this thread, while using absolutely none of the features of Excel. ---
**Author:** ![Reply](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/reply/32/15952_2.png) [@Reply](https://boards.straightdope.com/u/Reply)\ **Post date:** [April 11, 2014, 8:47pm UTC](https://boards.straightdope.com/t/is-there-a-way-to-make-excel-less-annoying-csv-files/685738/32 "2014-04-11T20:47:18Z")
> [@lazybratsche](#): > > Side question: Is there a decent alternative to Excel for making and editing simple CSV files? These days I’m often manually entering small-medium datasets into Excel, and saving as a CSV so I can do analysis with R. That means I’ve got to suffer the same constant-but-minor inconveniences described in this thread, while using absolutely none of the features of Excel. Google Sheets or OpenOffice Calc ---
**Author:** ![Tim\_T-Bonham.net](https://avatars.discourse-cdn.com/v4/letter/t/46a35a/32.png) [@Tim\_T-Bonham.net](https://boards.straightdope.com/u/Tim_T-Bonham.net)\ **Post date:** [April 13, 2014, 2:13am UTC](https://boards.straightdope.com/t/is-there-a-way-to-make-excel-less-annoying-csv-files/685738/33 "2014-04-13T02:13:17Z")
> [@lazybratsche](#): > > Side question: Is there a decent alternative to Excel for making and editing simple CSV files? CSV files are just text files – any good text editor will work for that. ---
**Author:** ![Civil\_Guy](https://avatars.discourse-cdn.com/v4/letter/c/d78d45/32.png) [@Civil\_Guy](https://boards.straightdope.com/u/Civil_Guy)\ **Post date:** [April 13, 2014, 4:55am UTC](https://boards.straightdope.com/t/is-there-a-way-to-make-excel-less-annoying-csv-files/685738/34 "2014-04-13T04:55:30Z")
I’ve just now myself been looking for a way to extract selected information from Excel files, and the way I’ve been trying out is by converting the files to csv, then extracting the data from them. What the heck, thinks I, I’ve got a desktop with Ubuntu/Linux, and I’d like to keep up on the latest, so I’ll see if I can extract the info by learning Python and coding up a simple script file. Yes, I’ve run into the OP’s situation, and I find it fairly annoying myself. No, I don’t suppose there’s much to be done about it. After I worked up a reasonably simple Python script to get the first part of what I need, I looked at the documentation again and I now see that Python has some kind of library specifically for reading and handling csv files. It looks like there’s a version of Python that can be installed on Windows machines, and some futher utility that can turn Python files into .exe programs, but I’ve haven’t really tried that stuff out. I doubt that this would be of much easy use to the OP, but it’s out there. ---
**Author:** ![Reply](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/reply/32/15952_2.png) [@Reply](https://boards.straightdope.com/u/Reply)\ **Post date:** [April 13, 2014, 9:09pm UTC](https://boards.straightdope.com/t/is-there-a-way-to-make-excel-less-annoying-csv-files/685738/35 "2014-04-13T21:09:15Z")
> [@Civil\_Guy](#): > > I’ve just now myself been looking for a way to extract selected information from Excel files, and the way I’ve been trying out is by converting the files to csv, then extracting the data from them. > > What the heck, thinks I, I’ve got a desktop with Ubuntu/Linux, and I’d like to keep up on the latest, so I’ll see if I can extract the info by learning Python and coding up a simple script file. > > Yes, I’ve run into the OP’s situation, and I find it fairly annoying myself. No, I don’t suppose there’s much to be done about it. > > After I worked up a reasonably simple Python script to get the first part of what I need, I looked at the documentation again and I now see that Python has some kind of library specifically for reading and handling csv files. > > It looks like there’s a version of Python that can be installed on Windows machines, and some futher utility that can turn Python files into .exe programs, but I’ve haven’t really tried that stuff out. > > I doubt that this would be of much easy use to the OP, but it’s out there. Wouldn’t excels built in programmability be better for stuff like that? ---
**Author:** ![Civil\_Guy](https://avatars.discourse-cdn.com/v4/letter/c/d78d45/32.png) [@Civil\_Guy](https://boards.straightdope.com/u/Civil_Guy)\ **Post date:** [April 14, 2014, 1:17am UTC](https://boards.straightdope.com/t/is-there-a-way-to-make-excel-less-annoying-csv-files/685738/36 "2014-04-14T01:17:53Z")
Not sure. The various xls files I’m data mining do not themselves have the macros or coding within themselves for extracting the info I’m looking for, so under any circumstance I’d somehow need to load each one into a spreadsheet that had the coding. I think. (And no, I’m not a great fan of VBA.) [Previous page](https://boards.straightdope.com/t/is-there-a-way-to-make-excel-less-annoying-csv-files/685738.md?page=1)