1

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.

Dave
  • 25,513
cheezsteak
  • 153
  • 3
  • 4
  • 11

1 Answers1

1

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.
EDM
  • 66