more than 15 numbers in a cell?

[Julie]

I am working with account numbers that have more than 15 digits. I am
working with office 2003 and after the 15 numbers it automatically makes the
numbers I enter a zero. I can't format the column to text for another reason,
does anyone know how I can format that cell to show all 20 numbers that I
enter in one cell?

Thanks!!

 

[Pete_UK]

The only way you can retain all 20 digits is to treat the number as
text. If you can't format the cell to Text beforehand, then you can
enter the number with a preceding apostrophe, like so:

'01234567890123456789

The apostrophe will not show in the cell, but Excel will treat it as a
text value.

Hope this helps.

Pete

 

[ryguy7272]

http://office.microsoft.com/en-us/excel/ha011366191033.aspx

Once you convert numbers to text you can't perform calculations on that text.

Good luck,
Ryan---

 

[Pete_UK]

It's quite rare to need to perform calculations on account numbers.

Pete

 

[sankar adhikary]

please give reply

I am working with account numbers that have more than 15 digits. I am
working with office 2003 and after the 15 numbers it automatically makes the
numbers I enter a zero. I cannot format the column to text for another reason,
does anyone know how I can format that cell to show all 20 numbers that I
enter in one cell?

Thanks!!

On Thursday, December 03, 2009 5:44 PM Pete_UK wrote:
The only way you can retain all 20 digits is to treat the number as
text. If you cannot format the cell to Text beforehand, then you can
enter the number with a preceding apostrophe, like so:

'01234567890123456789

The apostrophe will not show in the cell, but Excel will treat it as a
text value.

Hope this helps.

Pete

the
son,

//office.microsoft.com/en-us/excel/ha011366191033.aspx[/url]

Once you convert numbers to text you cannot perform calculations on that text.

Good luck,
Ryan---
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Pete_UK" wrote:

 

[joeu2004]

please give reply

Please take some time to state your question.

What part of the previous answers do you not understand?

When inputing numbers, Excel will only interpret the first 15
significant digits. Any remaining digits will be treated as zero.

So 1234567890123456 will be interpreted as 1234567890123450. Now
that ...56 is __not__ rounded to ...60.

There are only two ways to avoid that; both force Excel to treat the
numeric string as text, not a number. The two ways are:

(a) Format the cell as Text before entering the data; or

(b) Enter the data prefixed with an apostrophe (aka single-quote).
For example, '1234567890123456.

This will not get you more precision if you reference the text in an
arithmetic expression. For example, if A1 contains the text
'1234567890123456, =A1*2 results in the number 2469135780246900, not
2469135780246912.

Note: Contrary to a previous comment, you __can__ refer to a numeric
string (text) in arithmetic expression. However, some Excel functions
will still treat it as text. For example, =A1+A1 results in the
number 2469135780246900, but =SUM(A1,A1) results in zero. Moreover,
the numeric string (text) will be treated as text in comparision. For
example, =A1>1E+300 returns TRUE because in Excel, text is always
considered greater than numbers.

Consequently, it is best not to expect to use numeric strings (text)
as numbers in expressions. This form should be used for "numbers"
that are actually identifiers, e.g. account numbers.

 

[Gord Dibben]

Reply to what?

Both responders...Pete and Ryan......have explained Excel's treatment of numbers
with greater than 15 digits.


Gord Dibben MS Excel MVP

please give reply

I am working with account numbers that have more than 15 digits. I am
working with office 2003 and after the 15 numbers it automatically makes the
numbers I enter a zero. I cannot format the column to text for another reason,
does anyone know how I can format that cell to show all 20 numbers that I
enter in one cell?

Thanks!!

On Thursday, December 03, 2009 5:44 PM Pete_UK wrote:
The only way you can retain all 20 digits is to treat the number as
text. If you cannot format the cell to Text beforehand, then you can
enter the number with a preceding apostrophe, like so:

'01234567890123456789

The apostrophe will not show in the cell, but Excel will treat it as a
text value.

Hope this helps.

Pete

the
son,

//office.microsoft.com/en-us/excel/ha011366191033.aspx[/url]

Once you convert numbers to text you cannot perform calculations on that text.

Good luck,
Ryan---
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Pete_UK" wrote:

On Friday, December 04, 2009 4:41 AM Pete_UK wrote:
it is quite rare to need to perform calculations on account numbers.

Pete

wrote:
ext.
.
am
kes the
reason,
at I

 

[Pete_UK]

And it was some 16 months ago. Although I still pop in most days and
post now and then, I've not seen Ryan post here for some time.

Pete

Reply to what?

Both responders...Pete and Ryan......have explained Excel's treatment of numbers
with greater than 15 digits.

Gord Dibben     MS Excel MVP

please give reply

On Thursday, December 03, 2009 4:44 PM Julie wrote:
I am working with account numbers that have more than 15 digits.  I am
working with office 2003 and after the 15 numbers it automatically makes the
numbers I enter a zero. I cannot format the column to text for anotherreason,
does anyone know how I can format that cell to show all 20 numbers that I
enter in one cell?
Thanks!!
On Thursday, December 03, 2009 5:44 PM Pete_UK wrote:
The only way you can retain all 20 digits is to treat the number as
text. If you cannot format the cell to Text beforehand, then you can
enter the number with a preceding apostrophe, like so:
'01234567890123456789
The apostrophe will not show in the cell, but Excel will treat it as a
text value.
Hope this helps.
Pete
the
son,
//office.microsoft.com/en-us/excel/ha011366191033.aspx[/url]
Once you convert numbers to text you cannot perform calculations on that text.
Good luck,
Ryan---
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.
:
On Friday, December 04, 2009 4:41 AM Pete_UK wrote:
it is quite rare to need to perform calculations on account numbers..
Pete
wrote:
ext.
.
am
kes the
reason,
at I- Hide quoted text -

- Show quoted text -