This question already has an answer here:

How can I stop Microsoft Excel from auto formatting data when imported from a text file? Specifically, I want it to treat all of the values as text.

I am auditing insurance data in excel before it is uploaded to the new database. The files come to me as tab delimited text files. When loaded, Excel auto-formats the data causing leading 0's on Zip Codes, Routing Numbers and other codes, to be chopped off.

I don't have the patience to reformat all of the columns as text and guess how many zeros need to be replaced. Nor do I want to click through the import wizard an specify that each column is text.

Ideally I just want to turn off Excel's Auto-Formatting completely, and just edit every cell as it were plain text. I don't do any formula's or charts, just grid plain text editing.

marked as duplicate by nixda, Dave, Tog, nc4pk, Simon Sheehan Oct 25 '13 at 0: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.


This should work for you: http://office.microsoft.com/en-us/excel-help/undo-or-turn-off-automatic-formatting-HA102491299.aspx#_Toc288715973

When Excel applies the automatic formatting, you can click the AutoCorrect Options button Button image that appears and choose to:

  • Undo the formatting (and you choose to redo it after you undo it) for this instance only
  • Change the specific AutoFormat options globally by clicking the stop option so that Excel stops making this change
  • Change the options for Excel by clicking Control AutoFormat Options.
  • this does not affect the formatting applied when loading a CSV. – Tim Richardson Apr 12 '16 at 1:34

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