Opening a tab-delimited data file in Excel to view &amp; manipulate the data is a very common operation around here.  However, by default Excel (2003/4 or 2007/8) will read the columns in a "General" format, which occasionally does terrible things like turning "1/2" into "2-Jan".    Is there a way to tell Excel never to do this, but always process the values as Text, without going through the format wizard, selecting all of the columns, and doing it manually?    Extra points if this works in both Mac and Windows versions of Excel.
65603
Though I try to avoid it, I occasionally have to open a CSV file in Excel. When I do, it formats columns containing numbers, which makes them useless for my purposes. As far as I can tell, the only way to prevent this from happening on import is to rename the file so the extension isn't .csv and use the import wizard to specify the format of each column individually. For files with 50-60 columns, this is impractical.  Since every answer for this oft-asked question on the internet suggests either some means of converting the formatted numbers back once the file is open (which won't work for me - I want to solve the general problem, not a few specific cases) or manually selecting the format type of each column (which I don't want to do), I'm looking for a way to set a global preference or style such that all columns of all CSV files opened are always formatted as text.  I know about "armoring" the numbers with quotes, too, but the files I get don't come like that and I was hoping to avoid having to pre-process the files so Excel doesn't screw them up.  Is there a way to do specifically this: Always format all columns in opened CSV files as text, without manually selecting each column every time during import?  I'm using Excel 2003, but I'll take answers for 2007 if that's what you know.
75539
I have a text file with tab-separated columns which I'd like to process using awk. Here's an example of such a file: size=1\tname=foo\tweight=1.2 weight=2.5\tname=bar\tsize=2  What I want to achieve is to normalize the numeric value in columns whose content is like $field_name=&lt;number&gt; to four decimal places and keep the rest as is. Here, $field_name is a shell variable that is passed to awk and I'd like to use its value inside a regex. Here's a snippet (which is not working of course). I'm particularly interested in fixing line #5 in the following awk script and not solutions using other tools, such as sed, perl, python, etc. $ cat &quot;${file}&quot; \                                       # 1     | awk -F &quot;\t&quot; -v field_name=&quot;${external_var}&quot; '     # 2       {                                                 # 3         for (i = 1; i &lt;= NF; i++) {                     # 4           if ($i ~ /$field_name=[0-9]*.?[0-9]+/) {      # 5             split($i, kv, &quot;=&quot;)                          # 6             $i = sprintf(&quot;%s=%.4f&quot;, kv[1], kv[2])       # 7           }                                             # 8         }                                               # 9         print $0                                        # 10       }'
25015
This is the problem I'm seeing:      Error - Unable to parse query string for function QUERY parameter 2: NO_COLUMND     The formula is of the form:  =query(importange("...", "Sheet!A:V"), "select D where V = 1", 1)   I imported data from another sheet, I want this current sheet to display column D when Column V is 1.  Is there a different way rather than my current formula?
34953
I have a spreadsheet with many columns. The columns are all a small standard width. Double-clicking the partition line between columns will expand the column width to encompass the longest string in that column.  Is there a shortcut to perform this operation on every column in the spreadsheet?
22288
I work with CSV files and sometimes need to quickly check the contents of a row or column from the command line.  In many cases cut, head, tail, and friends will do the job; however, cut cannot easily deal with situations such as  "this, is the first entry", this is the second, 34.5   Here, the first comma is part of the first field, but cut -d, -f1 disagrees. Before I write a solution myself, I was wondering if anyone knew of a good tool that already exists for this job.  It would have to, at the very least, be able to handle the example above and return a column from a CSV formatted file.  Other desirable features include the ability to select columns based on the column names given in the first row, support for other quoting styles and support for tab-separated files.  If you don't know of such a tool but have suggestions regarding implementing such a program in Bash, Perl, or Python, or other common scripting languages, I wouldn't mind such suggestions.
80572
I have an Excel file with Unicode content of which some cells contain text inside double quotes, for example "text".   When I save the Excel file to a text file in Unicode format, the text which contains the double quote is saved as three double quotes, for example """text""".  There are some places where I even have text which contains comma (,). For example, text, which is converted into "text," in the Unicode file. It is adding double quotes to the text, which I guess contains special characters.  How can I avoid this?
84648
I've read many posts about using pivot to get the data in the format I want, but the more I read the more confused I get.  I have this data:    That I'm trying to get into a format similar to this:   For the most part, everything I try results in an SQL error, and the only successful attempt I've had didn't return the data in the format I'm looking for.  Any help would be appreciated.
44305
I imported the coordinates from an Excel table. They show as single points as intended. Now I want to add columns to the attribute table, but I'm not able to edit it. I wanted to import the following columns from Excel as well, but that doesn't seem to work either.  Sorry for the noob-question, but I couldn't find anything relating the theme through google and such.  Ok, with pictures then: I want to use that to add columns with information to these       You see, the small pensil thingy is greyed out, so I can't edit anything in this layer and I can't add any information to my points.  If I try Ale suggestion I get this error:
45996
Every CSV I've ever tried to save from Excel (2010, anyway) says filename.csv may contain features that are not compatible with CSV (Comma delimited). Do you want to keep the workbook in this format?. Why is this? Is there any way to save CSV files from excel that do not contain this message?  Even the following file (saved as a .csv) will cause this to happen, if you try to save it:  test1,test2 test3,test4   I've even just created a new spreadsheet in excel and tried to save it, and it still shows this message.   I wouldn't care, but I have someone telling me that I'm not formatting CSV files properly because excel shows that warning message, and I'm looking for ammunition to prove it's Excel and not my format (or if it actually is my format, to fix it).
56187
I've got multiple-line text files of (sometimes) tab-delimited data.  I'd like to output the file so I can glance over it - so I'd like to only see the first 80 characters of each line (I designed the text file to put the important stuff first on each line).  I'd thought I could use cat to read each line of the file, and send each line to the next command in a pipe:  cat tabfile | cut -c -80   But that seemed broken.  I tried monkeying around, and grep appeared to work - but then I found out that, no it didn't (not every line in the file had 80+ characters) - it appears tabs are counted as single characters by cut.  I tried:  cat tabfile | tr \t \040 | cut -c -80   Even though that would mangle my data a bit, by eliminating the white-space readability.  But that didn't work.  Neither did:  cat tabfile | tr \011 \040 | cut -c -80   Maybe I'm using tr wrong?  I've had trouble with tr before, wanting to remove multiple spaces (appears the version of tr that I have access to on this machine has an -s option for squeezing down multiple characters - I may need to play with it more)  I'm sure if I messed around I could use perl, awk or sed, or something to do this.  However, I'd like a solution that uses (POSIX?) regular commands, so that it's as portable as possible.  If I do end up using tr, I'd probably eventually try turning tabs into characters, maybe do a calculation, cut on the calculation, and then turn those characters back into tabs for the output.  It doesn't need to be a single line / entered directly on the command line - a script is fine.    More info on tab-files:  I use tab to break fields, because someday I may want to import data into some other program.  So I tend to have only one tab between pieces of content.  But I also use tabs to align things with vertical columns, to aid in readability when looking at the plain text file.  Which means for some pieces of text I pad the end of the content with spaces until I get to where the tab will work in lining up the next field with the ones above and below it.   DarkTurquoise           #00CED1         Seas, Skies, Rowboats   Nature MediumSpringGreen       #00FA9A         Useful for trees        Magic   Lime                    #00FF00         Only for use on spring chickens and fru$
80165
How do I turn a column of urls into hyperlinks, so that users can click on the url in a cell and have it open in a browser?  I pasted 100 urls and each went into its own cell. When I double-click in the cell and then leave it, Excel turns the text blue and makes a link out of it. I don't want to double-click a hundred times, but still want to format all the cells into links.
73620
How can I prevent the contents of specific cells (or entire row or column) from printing in Excel, while keeping them visible on the screen when I'm editing the file? Ideally I should be able to do this to cells in the interior of the spreadsheet (i.e., not just the rightmost column(s) or the bottom row(s)). Hiding row(s) or column(s) is not acceptable because I need to maintain the cell size, to maintain the overall appearance of the sheet.  I've looked for a way to define a conditional format that's conditional on whether Excel is currently printing, but there doesn't seem to be any such thing. I've been manually setting the text color to white before printing (and manually reverting it afterwards), but it seems there should be an automagic way of doing this.
112650
Whenever one of my end users clicks "Export Details" or "Printable View" on a report and chooses .xls (default), the report will download. When she clicks on it to open, Excel opens but the area where the report should be is blank and gray. This happens will all Salesforce reports she exports. Exporting to .csv is working just fine. Also to note, she has Windows 7 Professional and Microsoft Excel 2010.  I logged in as her from my laptop and was unable to replicate the issue as everything worked fine for me. We did a screensharing session and I logged in as myself on her computer, but we still encountered the issue.  Any other .xls files are opening just fine for her in Excel. She mentioned she did a Windows update yesterday and that might have something to do with it. It's just curious that it's only happening with Salesforce reports.  Has anybody else encountered this issue? Do you think this is more of a laptop problem or a Salesforce problem?
93794
I regularly copy and paste text between spreadsheets, emails, browser windows, etc. I can't think of a single time when I've wanted to keep the formatting from the source text.  I already know about the following workarounds:   Use “Edit” → “Paste Special” → “Unformatted text” Paste to Notepad and copy from there Install a program that takes over the clipboard   What I want is to tell Windows to just do this by default.  Is this possible?
37670
This problem is simple to correct but is very very annoying what I'm doing is to type a hour range, eg. (0-1),(1-2),(2-3) so on per cell row     DATE ---   HOUR    1/1/2013  0-1  1/1/2013  1-2 1/1/2013  2-3 1/1/2013  3-4   Excel stupidly keeps formatting 1-2 to a date 2-Jan to make matters worse is when I change it to a number format it gives 41276  I also cannot find the AutoFormat option in excel 2010, I am aware of the Format button in home but there is no autoformat there.
104750
I have an Excel file that has a bunch of VBA and macro code in it.  When I open the file in Excel I can choose not to 'enable' them - so the values in the fields all stay as they were during the last save.  I need to manipulate the values as they were last saved - so I don't want the macros (which look at the current date and update values accordingly) to run.  When I open it via our dot net code:   Dim oxlRep As Excel.Application Dim oWBRep As Excel.Workbook Dim oSheetRep As Excel.Worksheet Dim oRngRep As Excel.Range oxlRep.Open(path)   the vb code runs - throwing off the values.  I've been looking for a way to open it without macros, or in 'secure' mode where the macros aren't run.  If I simply double click the file and don't choose to enable macros the values are all there as I want them.    Usually we run this code within the month that the files are created, so we haven't seen this problem in the 3 or 4 years that it has been working.  Now I need to go back to some of the old files and run some archival code...    Anyone have a suggestion?
42644
I have more than 10,000 rows of data I need to do some calculations on one column of the data. I have made the calculation in one cell and now need to fill the formula for the calculation and all the cells.  Usually I just select the cells using the keyboard and then press the Ctrl+D to fill the cells. However, now I have to do this repeatedly, and it is difficult to keep on selecting 5000 rows each file. Is there a simpler way where I can just specify the range using the keyboard? Perhaps even have a macro since all of the files have a fixed length  Also, since I have difficulty in using the mouse because of medical reasons and therefore I have not tested  which involves clicking on a very small fill handle. Maybe there is a parallel solution to this using the keyboard or maybe I can create a macro which will automatically click on a fill handle of a selected cell - please advise if there is a way.