Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

This question already has an answer here:

Opening a tab-delimited data file in Excel to view & 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.

share|improve this question

marked as duplicate by nixda, teylyn, Jens Erat, Raystafarian, gronostaj Jul 15 '13 at 9:39

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

    
what creates these original tab-delimited files? AdamV's answer could be setup once and work. Or do you just need ANY tab-delimited file to never use general as a column format? –  datatoo Sep 30 '10 at 15:35
    
@datatoo: These are either input files coming in from clients, or output files we're sending back to them. I won't swear I don't ever want Excel to not process them, but the vast majority of the time, I want "1/2" to stay "1/2". (re-reads...) You know, I had glossed over the "template" aspect of AdamV's answer. I may have to look at that again! Thank you. –  khedron Oct 6 '10 at 20:27
add comment

2 Answers 2

If you enclose the value in double quotes with single quotes, it would treat it as text. However the single quote would be visible. If this OK, then try out the below format;

abc,"'1/2",xys
sss,"'2/3",pqr

The 1/2 would show up as '1/2 and does not show up as 2-Jan

share|improve this answer
    
You're right, that would work, but I would rather not change all code which writes out tab-delimited files to put quotes around things Excel might interpret as values. Excel is just a convenient viewer, it's not actually the end consumer of this process, if you know what I mean. Thank you, though! –  khedron Oct 21 '10 at 17:58
add comment

Jan Karel Pieterse has a great article about using a proper external data query from text files which may well suit your needs.

http://www.jkp-ads.com/articles/importtext.asp

You could include this in a template for example so that you could do file > new > [import workbook] > refresh > choose file to import from > job done!

share|improve this answer
    
That looks like a nice tutorial! Unfortunately, as part of it he steps through the Import Wizard and explicitly sets the column types, which is the part I was trying to automate, template-ize, or otherwise not have to do. Thank you, though! –  khedron Apr 10 '10 at 17:02
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.