Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 150
Default more than 15 numbers in a cell?

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!!
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default more than 15 numbers in a cell?

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

On Dec 3, 9: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 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!!


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,836
Default more than 15 numbers in a cell?

http://office.microsoft.com/en-us/ex...366191033.aspx

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

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


"Pete_UK" wrote:

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

On Dec 3, 9: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 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!!


.

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default more than 15 numbers in a cell?

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

Pete

On Dec 4, 2:41*am, ryguy7272
wrote:
http://office.microsoft.com/en-us/ex...366191033.aspx

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

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



"Pete_UK" wrote:
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


On Dec 3, 9: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 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!!


.- Hide quoted text -


- Show quoted text -


  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default more than 15 numbers in a cell?

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 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,



On Thursday, December 03, 2009 9:41 PM ryguy7272 wrote:


http://office.microsoft.com/en-us/ex...366191033.aspx

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






  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,059
Default more than 15 numbers in a cell?

On Apr 6, 11:45*pm, sankar adhikary wrote:
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 a

(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, =A11E+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.
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 621
Default more than 15 numbers in a cell?

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

On Thu, 07 Apr 2011 06:45:53 GMT, sankar adhikary wrote:

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 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,



On Thursday, December 03, 2009 9:41 PM ryguy7272 wrote:


http://office.microsoft.com/en-us/ex...366191033.aspx

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



  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default more than 15 numbers in a cell?

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

On Apr 7, 3:43*pm, Gord Dibben wrote:
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



On Thu, 07 Apr 2011 06:45:53 GMT, sankar adhikary wrote:
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 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,


On Thursday, December 03, 2009 9:41 PM ryguy7272 wrote:


http://office.microsoft.com/en-us/ex...366191033.aspx


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- Hide quoted text -


- Show quoted text -


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
add multiple numbers in one cell with those numbers remaining visi TD Excel Worksheet Functions 7 May 28th 09 09:41 PM
Selecting specific numbers from a cell containing multiple numbers JRD Excel Worksheet Functions 3 January 18th 09 01:32 AM
how to extract numbers from imported cell with text and numbers? jyin Excel Discussion (Misc queries) 3 March 28th 07 01:14 PM
Help! How do you get excel to find the x(changes daily, marked in a cell from another formula) highest numbers in a group of numbers and sum them up? C-Man23 Excel Worksheet Functions 3 January 19th 06 10:52 AM
Help! How do you get excel to find the x(changes daily, marked in a cell from another formula) highest numbers in a group of numbers and sum them up? C-Man23 Excel Worksheet Functions 1 January 9th 06 02:23 PM


All times are GMT +1. The time now is 04:14 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"