How to stop Excel from automatically truncating leading zeros


K

Kenny A.

Is there a setting in Excel 2007 to keep it from automatically trunkating
leading zeros like when pasting in zip codes or department codes. When Excel
sees what it thinks is a number, it treats it like a number and drops leading
zeros. Is there a setting in Options or somewhere to tell excel to leave the
leading zeros. We open lots of CSV files in EXCEL and it sees a number (such
as a department code) and drops the leading zeros. In simple, does Excel have
a setting to prevent this from happening as a default????

Thanks
Kenny
 
Ad

Advertisements

M

Mike H

In simple, does Excel have
a setting to prevent this from happening as a default????

Simply, No.

But there are things you can do. You can format the range as text and
leading zeroes will be retained.

If your numbers are a fixed length (say) 9 digits you can apply a custom
format of 000000000 that's 9 zeroes. Now the number 1 will show as 000000001

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
 
Ad

Advertisements

G

Gord Dibben

No default for this.

Are you able to change the extension of the *.csv files to *.txt

If so, when you open them the Text Wizard will open and you can designate
text formatting for each column.


Gord Dibben MS Excel MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top