Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
add multiple numbers in one cell with those numbers remaining visi | Excel Worksheet Functions | |||
Selecting specific numbers from a cell containing multiple numbers | Excel Worksheet Functions | |||
how to extract numbers from imported cell with text and numbers? | Excel Discussion (Misc queries) | |||
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? | Excel Worksheet Functions | |||
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? | Excel Worksheet Functions |