Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-02-2017, 03:12 AM
Jo Freeman Jo Freeman is offline Entering a number of 19 digits Windows 7 64bit Entering a number of 19 digits Office 2016
Novice
Entering a number of 19 digits
 
Join Date: Mar 2015
Posts: 19
Jo Freeman is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 11-02-2017, 04:47 AM
NBVC's Avatar
NBVC NBVC is offline Entering a number of 19 digits Windows 10 Entering a number of 19 digits Office 2013
The Formula Guy
 
Join Date: Mar 2012
Location: Mississauga, CANADA
Posts: 215
NBVC will become famous soon enoughNBVC will become famous soon enough
Default

Are you sure the format of the cell is text? If it is then it would work.
Reply With Quote
  #3  
Old 11-02-2017, 07:13 AM
Jo Freeman Jo Freeman is offline Entering a number of 19 digits Windows 7 64bit Entering a number of 19 digits Office 2016
Novice
Entering a number of 19 digits
 
Join Date: Mar 2015
Posts: 19
Jo Freeman is on a distinguished road
Default

Quote:
Originally Posted by NBVC View Post
Are you sure the format of the cell is text? If it is then it would work.
Hi NBVC

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.
Reply With Quote
  #4  
Old 11-02-2017, 07:15 AM
Jo Freeman Jo Freeman is offline Entering a number of 19 digits Windows 7 64bit Entering a number of 19 digits Office 2016
Novice
Entering a number of 19 digits
 
Join Date: Mar 2015
Posts: 19
Jo Freeman is on a distinguished road
Default

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.
Reply With Quote
  #5  
Old 11-02-2017, 08:47 AM
ArviLaanemets ArviLaanemets is offline Entering a number of 19 digits Windows 8 Entering a number of 19 digits Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

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.
Reply With Quote
  #6  
Old 11-02-2017, 09:21 AM
Jo Freeman Jo Freeman is offline Entering a number of 19 digits Windows 7 64bit Entering a number of 19 digits Office 2016
Novice
Entering a number of 19 digits
 
Join Date: Mar 2015
Posts: 19
Jo Freeman is on a distinguished road
Default

Got it, thank you Avril
Reply With Quote
  #7  
Old 11-03-2017, 02:25 PM
joeu2004 joeu2004 is offline Entering a number of 19 digits Windows 7 32bit Entering a number of 19 digits Office 2007
Advanced Beginner
 
Join Date: Aug 2016
Posts: 32
joeu2004 is on a distinguished road
Default

Quote:
Originally Posted by Jo Freeman View Post
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.
Correct. But you can alter the text afterwards.

Quote:
Originally Posted by ArviLaanemets View Post
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
Ordinarily, that is correct.

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
Reply With Quote
  #8  
Old 11-03-2017, 11:55 PM
ArviLaanemets ArviLaanemets is offline Entering a number of 19 digits Windows 8 Entering a number of 19 digits Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

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.
Reply With Quote
  #9  
Old 11-04-2017, 11:12 AM
joeu2004 joeu2004 is offline Entering a number of 19 digits Windows 7 32bit Entering a number of 19 digits Office 2007
Advanced Beginner
 
Join Date: Aug 2016
Posts: 32
joeu2004 is on a distinguished road
Default

Quote:
Originally Posted by ArviLaanemets View Post
there is a workaround for 15-number limit. [....] 1. Divide the number to 2 parts [....] P.e. to make calculations with integers like 123456789012345678 enter it as 123456789 and 12345678
Not sure what you have in mind, because you neglected to show a complete example of how you would combine them later in a calculation, if that is your intent.

(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:
Originally Posted by ArviLaanemets View Post
2. Design formulas (or user-defined functions) for mathematical operations with such number pairs
No need to split "numbers". Just enter the complete number as text. If we can bother to write 1234567890 and 12345678 into separate cells, we could simply write '123456789012345678 (note the leading apostrophe) or ="123456789012345678", if not set the cell format to Text.

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
Reply With Quote
  #10  
Old 11-06-2017, 03:39 AM
Jo Freeman Jo Freeman is offline Entering a number of 19 digits Windows 7 64bit Entering a number of 19 digits Office 2016
Novice
Entering a number of 19 digits
 
Join Date: Mar 2015
Posts: 19
Jo Freeman is on a distinguished road
Default

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.
Reply With Quote
  #11  
Old 11-06-2017, 11:43 AM
joeu2004 joeu2004 is offline Entering a number of 19 digits Windows 7 32bit Entering a number of 19 digits Office 2007
Advanced Beginner
 
Join Date: Aug 2016
Posts: 32
joeu2004 is on a distinguished road
Default

Quote:
Originally Posted by Jo Freeman View Post
to be honest, some of the discussion was way too complicated for me to understand!
And not at all relevant to your problem.

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.
Reply With Quote
  #12  
Old 11-08-2017, 06:20 AM
Jo Freeman Jo Freeman is offline Entering a number of 19 digits Windows 7 64bit Entering a number of 19 digits Office 2016
Novice
Entering a number of 19 digits
 
Join Date: Mar 2015
Posts: 19
Jo Freeman is on a distinguished road
Default

You are right. I was entering SIM numbers for mobile phones - no arithmetic involved.
Reply With Quote
Reply

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
Entering a number of 19 digits Digits are not found in my document! reza8615953 Word 3 04-18-2017 10:48 AM
Entering a number of 19 digits 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
Entering a number of 19 digits Problems merging in last 4 digits of an account higher than 16 digits Glynda Mail Merge 1 04-08-2011 12:17 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 10:26 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft