#1
|
|||
|
|||
Entering a number of 19 digits
Hi
I know I can't enter a number longer than 15 digits, but when I alter the cells to "Text" and enter the number, I get this: 1.23457E+17 Can someone please tell me how to get the number to show in its entirety? Many thanks, Jo |
#2
|
||||
|
||||
Are you sure the format of the cell is text? If it is then it would work.
|
#3
|
|||
|
|||
Quote:
This is really strange. I entered the 19-digit number and clicked on Format, Format Cells, Number, Text and the above result appeared. When I altered another cell to Text, using the dropdown box on the Home tab, and entered the number it worked fine. Don't understand the difference there, but I am getting the result I want using the second method. Thank you for your reply. |
#4
|
|||
|
|||
Actually, I have just had an epiphany! You need to make sure the cell is a Text cell before entering a long number. You can't alter it afterwards.
|
#5
|
|||
|
|||
when you change the format of cell(s) to cardinally different one, like number to text or other way around, the actual values entered before dont get new formats automatically. to change the format of those entries you have.
a) edit the entry, i.e. select a cell and press F2, and then press Enter, or double-click on cell, and then press Enter; or b) make some operations on cell(s) with changed format. p.e. to format previous texts to numbers you copy number 1 into some empty cell, copy the cell, select the range where you changed formats and then use PasteSpecial Multiply operation on this range. Or to format previous numbers to text you enter into some free cell the formula = "" & CellWithNumber, copy it to range so all numbers are converted, and then use PasteSpecial Values operation to overwrite numbers in range with changed format with calculated ones. When you change p.e. numbers to dates by merely formatting, this is not needed, because really dates are same numbers - only displayed in different way. |
#6
|
|||
|
|||
Got it, thank you Avril
|
#7
|
|||
|
|||
Quote:
Quote:
However, if the "number" has more than 15 digits, Jo is correct: we must set the cell format to Text before entering the data. Alternatively, prefix the "number" with an apostrophe (single-quote; the character ' ). Otherwise, we have already lost precision -- the digits to the right of the first 15 digits. Those digits are irretrievable. For data entry (manually or opening a text file, including a CSV file), Excel interprets only the first 15 significant digits, replacing any digits to the right with zero, effectively truncating after the 15th digit. (It is better to import, not open, text files. Then in the last menu, we can select a column that has long "numbers" to be treated as Text.) Moreover, when the "number" with more than 15 digits is entered as text, we do not get any more precision if we reference that cell in an arithmetic expression. Again, Excel interprets only the first 15 significant digits. The same is true of the VALUE function. ----- Jo, if you truly want a numeric value with more than 15 digits, bear in mind that Excel cannot represent integers greater than 2^53 (9007199254740992) accurately. That is most 16-digit numbers. But it can do so only when the value is the result of arithmetic; for example, =9007199254740990+2. (Errata.... I should have written "cannot represent all integers greater than 2^53". Certainly, some integers greater than 2^53 can be represented exactly.) Moreover, Excel formats only the first 15 significant digits (rounded), replacing any digits to the right with zeros. That is why 2^53 appears to be 9007199254740990, but in fact it is 9007199254740992. Last edited by joeu2004; 11-04-2017 at 01:20 PM. Reason: Errata |
#8
|
|||
|
|||
Btw., there is a workaround for 15-number limit. A cumbersome one, but when someone needs to make exact mathematical calculations with such numbers, then:
1. Divide the number to 2 parts and enter them in separate cells (columns). (you can also enter the long number as text, and use formulas to divide it.) P.e. to make calculations with integers like 123456789012345678 enter it as 123456789 and 12345678, or to make calculations with decimals like 0.123456789012345678 enter it as 0,123456789 and 12345678 (or 0.012345678); 2. Design formulas (or user-defined functions) for mathematical operations with such number pairs. NB! You need 2 formulas for one operation. And when you write a function, then it must have a parameter, determining which part of result is returned. You can then use formula to get result returned as long number in text format too. |
#9
|
|||
|
|||
Quote:
(Not sure that is Jo's intent, anyway.) There is no getting around the fact that Excel stores numeric values as 64-bit binary floating-point, and the precision is limited to 53 consecutive powers of 2 ("bits") times a binary exponential factor. That limits any and all attempts to combine your terms into an accurate integer to any integer that is 2^53 or less, and to only some integers greater than 2^53. In your example of representing 123456789012345678 with integers 1234567890 (corrected) in A1 and 12345678 in A2, the numeric value must eventually become 123456789012345678, which we might write =A1*1E8+A2. The exact decimal presentation of the resulting binary value is 123456789012345680. (In the old days, we would write multiprecision algorithms that used integer parts that were limited to 16 bits. Much easier to do in assembly language, IMHO. But I dare say the mathematics are beyond the ability of most programmers today, not to mention the casual user.) That is better than 123456789012345000 (actually 123456789012344992 after binary conversion), which we get if we enter it manually. But it is still not 123456789012345678. (PS.... The close similarity of 123456789012345680 is coincidence. Here's another example: 6118447641 and 26275648 combine to be 611844764126275648. The exact decimal presentation of the binary value is 611844764126275584.) And equally important: Excel will display the numeric value as 123456789012346000, which I suspect Jo would dislike. I think this is a distraction. Jo seems content with entering the large "number" as text. She just wanted to know how. I was merely noting that your suggest to enter the number first, change the cell format to Text, then press f2 Enter would not work because the excess precision is already lost. Jo hit upon the correct solution herself: set the cell format to Text first. Quote:
There are VBA data types that work with more precision, notably type Decimal and the CDec function. But the result will be converted to 64-bit binary floating-point if it is stored as a number in Excel. To preserve the precision, we must store the result as text. No need to reinvent the wheel. There are add-ons that provide a library of functions that do just that. But the point is: the result is text. And again, any attempt to convert the text to a numeric value in Excel will be faced with the 64-bit binary floating-point limits noted above and in my previous posting. Last edited by joeu2004; 11-05-2017 at 07:56 AM. Reason: PS |
#10
|
|||
|
|||
Well, many thanks to all of you for taking the time to look at my problem although to be honest, some of the discussion was way too complicated for me to understand!
I would just like to say what a great forum this is. So many people willing and able to offer advice to inexperienced users like me. I'm really grateful for your time. |
#11
|
|||
|
|||
Quote:
The take away is.... 1. You are right: format the cell as Text first, then enter the long "number" as text. 2. Do not try to use the long "number" in an arithmetic expression; you will lose accuracy. I don't think you would: I presume the long "number" is actually an ID. |
#12
|
|||
|
|||
You are right. I was entering SIM numbers for mobile phones - no arithmetic involved.
|
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Having number's digits together | mohsen.amiri | Word | 0 | 06-23-2017 01:20 AM |
Digits are not found in my document! | reza8615953 | Word | 3 | 04-18-2017 10:48 AM |
Entering a number in a cell causes it to fill with the color of a nearby cell | Bill Martz | Excel | 1 | 04-23-2015 07:57 PM |
Separate the digits into 3 combinations | Jasa P | Word VBA | 1 | 08-19-2012 11:04 PM |
Problems merging in last 4 digits of an account higher than 16 digits | Glynda | Mail Merge | 1 | 04-08-2011 12:17 AM |