4

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.

Michael H.
  • 381

2 Answers2

0

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!

AdamV
  • 6,396
0

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

Dheer
  • 602