Jun 16 2017 07:43 AM
Jun 16 2017 07:43 AM
Here's an example. I have a long number (it's from a license plate/bar code):
Whether I use my bar code software's export-excel function, or PASTE the number directly, here is what I get:
If I then format the cell as a number, I get:
As you can see, I have lost the '0862' at the end of my number. Any ideas?
Jun 16 2017 08:14 AM
Excel can only operate with 15 digits. Any digits beyond will be changed to 0.
Change the number format to Text.
Jun 16 2017 09:06 AM
Jun 16 2017 11:04 AM
That's by design, you may see up to 11 digits, see https://support.microsoft.com/en-us/help/2643223/long-numbers-are-displayed-incorrectly-in-excel
Jun 17 2017 03:59 AM
You might also like to add your vote here
Aug 02 2018 12:14 PM
Aug 21 2018 01:19 PM
In my experience, once the number is converted, you've lost the original number. If the cell is formatted as text prior to entering the number, you will retain the original value. If you have an existing .CSV file, you can use the Text Import Wizard (Data>Get External Data>From Text) to open the file, and set the column format as text during the import process, and it should retain the values as well.
Aug 21 2018 11:06 PM
Aug 22 2018 11:12 AM
No, due to the 15 digit limit for numbers in Excel
Aug 22 2018 03:36 PM
yes you're right!
no matter how long the number - in the cell it will only show 15 digits - the rest being zeroes...
Oct 19 2018 09:53 AM
I was having this same problem: 20 digit number was getting 00000s at the end or being stored in scientific notation.
The work around: format the cell as text and add a ' in front of the number string. The cell will not display the ' before the number and will display only the full number without zeros at the end.
Ex. 123456789123456789 was showing up as 123456789123456000 when formatted as anumber and showing in scientific notation when formatted as text. Changed cell formatting to text and entered '123456789123456789 as the cell value. Cell displays 123456789123456789 accurately afterwards.
Jan 03 2020 10:52 PM
when you will save in database that time convert value into toString() like
then it will save original value in database.
Jun 03 2020 10:19 AM
Hi @Keith Kargl
If you need detailed instructions, please see attached, in case you are familiar with the functionality in XL you will need to:
1- copy your long numbers into a .txt file. Save it somewhere on your machine (or in any cloud you can access it)
2- open XL, select from the "Data" menu the option "From Text/CSV". Search for your file and click "Import"
3- don't click on "Load", but choose "Transform Data", validate your Data Type states "Text" and then click on "Close & Load"
4- select all the cells that you need from the list, and do a Copy/ Paste Values.
Nov 16 2020 06:16 PM
@TS1970 Option 'C', use Google SHEETS, it does not have this nightmare problem. Previous versions of Excel pasted this sort of content correctly, then MS decided to make things unusable.
Jan 13 2021 04:43 PM
Dec 03 2021 06:24 PM
@Keith Kargl try use single quotes ' first and then paste your desired data
Dec 15 2021 03:59 AM
Thanks, the only solution that works.
Worth noting that you do not first have to save your list of long numbers to a text file - you can just copy them to the clipboard.
Then, after placing your cursor at the location where you'd like to paste them, use the dropdown menu under the Paste icon in the ribbon, then choose "Use Text Import Wizard" then follow the same steps in terms of parsing and choosing column types as you would for importing from a text file.
Apr 07 2022 02:39 AM
Not sure if you have solved this but for others struggling with the same problem. This has worked for me.
When copying over to excel or typing by selecting the block. ( You get the scientific number)
Then select the cell block formula bar above after the number and press enter. It should automatically correct the number to the proper format.
Hope this helps.
Apr 13 2022 03:21 PM
@lwoosleyAgree. Doesn't matter if you change to text.
Apr 14 2022 02:13 PM - edited Apr 14 2022 02:14 PMSolution
after pasting, click on the column, format cells, fraction and ok. That works for me. @Keith Kargl