How to stop Excel from automatically truncating leading zeros

[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

 

[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.

 

[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