Prev: Locking Cells
Next: Conditional Format - If day = 24 or 25
From: Myrddin on 25 Jan 2010 18:39
I want to keep the cell as a numerical number not in text format. I am trying
to set up bank account numbers in another system taking the raw data from
excel so need the zero at the front. How do I keep the zero?
From: clr on 25 Jan 2010 18:58
You can't really. Any string of digets with a leading zero is NOT a number,
it isTEXT. You can fake it by Custom formatting to make it LOOK like it has
a leading zero, but it's not really there.

BTW, why would you want to maintain a list of Bank Account numbers as
NUMBERS anyway.....you probably won't be doing any math on them.

Vaya con Dios,
Chuck, CABGx3

"Myrddin" <Myrddin(a)discussions.microsoft.com> wrote in message
news:4DC16E9E-E820-46AA-AA4B-A50CB84C45D7(a)microsoft.com...
>I want to keep the cell as a numerical number not in text format. I am
>trying
> to set up bank account numbers in another system taking the raw data from
> excel so need the zero at the front. How do I keep the zero?


From: Joe User on 25 Jan 2010 20:02
"clr" <croberts(a)tampabay.rr.com> wrote:
> You can't really. Any string of digets with a leading
> zero is NOT a number, it isTEXT.

Dovetailing Chuck's comments, the point is: it should be __entered__ as
text. This can be done one of several ways, depending on the data source.

1. Manually enter the account numbers with a leading apostrophe (aka single
quote). The apostrophe is not considered part of the date; but it does tell
Excel to treat the characters as text.

2. Format the cell as Text before entering the account numbers.

3. Select Text format when using the Import Data wizard.

4. If you already have numbers, enter a formula like
=TEXT(A1,"0000000000000000") in a parallel column,
copy-and-paste-special-value the new column over the old column, and delete
the new column.

But having said all that....


"Myrddin" <Myrddin(a)discussions.microsoft.com> wrote:
>> I am trying to set up bank account numbers in another
>> system taking the raw data from excel so need the zero
>> at the front. How do I keep the zero?

Exactly how are you "taking the raw data from Excel", and what do you mean
by "another system": application or computer?

Even though you do need to solve the problem of preserving leading zeros in
Excel, as explained above, that might not solve your real problem, namely:
preserving leading zero when you input the "raw data" into "another system".


----- original message -----

"clr" <croberts(a)tampabay.rr.com> wrote in message
news:ua3fYphnKHA.4124(a)TK2MSFTNGP02.phx.gbl...
> You can't really. Any string of digets with a leading zero is NOT a number,
> it isTEXT. You can fake it by Custom formatting to make it LOOK like it has
> a leading zero, but it's not really there.
>
> BTW, why would you want to maintain a list of Bank Account numbers as
> NUMBERS anyway.....you probably won't be doing any math on them.
>
> Vaya con Dios,
> Chuck, CABGx3
>
> "Myrddin" <Myrddin(a)discussions.microsoft.com> wrote in message
> news:4DC16E9E-E820-46AA-AA4B-A50CB84C45D7(a)microsoft.com...
>>I want to keep the cell as a numerical number not in text format. I am
>>trying
>> to set up bank account numbers in another system taking the raw data from
>> excel so need the zero at the front. How do I keep the zero?

 | 
Pages: 1
Prev: Locking Cells
Next: Conditional Format - If day = 24 or 25