How Do I Prevent Excel from Formatting My Text File?

**Ocean Zhang** · 07-23-2009, 04:21 PM

When I export my files onto text and open it with Excel, it will automatically format the cells to take out leading zeros, convert to scientific notation, and convert certain numbers into date/month/year format. How can I get Excel to open my file without reformatting the numbers in my text file?

Thanks
Ocean

**martindwilson** · 07-23-2009, 05:25 PM

dont open it directly with excel use the get external data from the data tab
choose from text
find your text file click import
when the wizard pops up
make sure delimited is checked
click next
click next again
click first field
use ctrl+shift click last field to select all fields
under column data format
check the text box
all fields should now say text instead of general
click finish
in the window that pops up choose where you want it to start import to (usually =$A$1 of existing worksheet
click ok

**teylyn** · 07-23-2009, 05:35 PM

Hi Ocean,

when you open a text file with Excel 2003, the text import wizard asks about delimeters, etc, and then presents a screen where you can go to each column and define what format the data should be, e.g. General (which will strip away leading zeros and use scientific notation for large numbers) or text (which will leave leading zeros in place, e.g. for phone numbers).

I'm not quite sure if 2007 opens that Wizard too, but I assume it should.