Prev: Custom Number Formats for Excel 2007
Next: Formula to return a formula
From: Kenny A. on 13 Apr 2010 17:31
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
From: Mike H on 13 Apr 2010 17:50
>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.


"Kenny A." wrote:

> 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
From: Gord Dibben on 13 Apr 2010 18:26
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

On Tue, 13 Apr 2010 14:31:02 -0700, Kenny A.
<KennyA(a)discussions.microsoft.com> wrote:

>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

 | 
Pages: 1
Prev: Custom Number Formats for Excel 2007
Next: Formula to return a formula