Forum Discussion
Solved
When I paste a long number, Excel turns it into scientific notation and changes numbers.
Here's an example. I have a long number (it's from a license plate/bar code):
00100018320523710862
Whether I use my bar code software's export-excel function, or PASTE the number directly, here is what I get:
1.00018E+17 |
If I then format the cell as a number, I get:
100018320523710000 |
As you can see, I have lost the '0862' at the end of my number. Any ideas?
after pasting, click on the column, format cells, fraction and ok. That works for me. Keith Kargl
- Even though this thread is 7 years old, this may help.
You barcode scanner can be set to include a prefix when you scan. Add the apostrophe as a prefix in your scanner settings and everything will be fine. - Select the column or row where you will paste the numbers then make the format TEXT. Paste the number and you will get your desired result.
Create a new column and format as a text: https://support.microsoft.com/en-gb/office/text-function-20d5ac4d-7b94-49fd-bb38-93d29371225c#:~:text=In%20its%20simplest%20form%2C%20the,code%20you%20want%20to%20apply%22)
Ex. If your values are in column L, row 27 then create a new column next to it (column M). And type the following: =TEXT(L27, ##########) and hit Enter.
Note: enter as many hashtags as you need to show all your digits
- On Mac - You can simply select a "special paste" by holding CTRL + CMD + V and choose "Text". This retains the long number form of the number.
I'm not sure what the hotkeys are for Windows, but essentially select the option for special paste (sometimes found in the top Edit menu next to File).- Excel Windows; Paste Special = Ctrl+alt+V
after pasting, click on the column, format cells, fraction and ok. That works for me. Keith Kargl
In modern Excel we have an option to disable such behaviour
If copy/paste the value from initial post it returns as
Hi Keith,
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.
Keith Kargl try use single quotes ' first and then paste your desired data
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.
DONE!
TS
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.
- wow.. thank you for your solution. Worked wonder .. no exactly the same steps from #3 but import from text file and specify and text column fixed it.
when you will save in database that time convert value into toString() like
Convert.toString(00100018320523710862)
then it will save original value in database.
- would pre-formatting the column or row to "Number" do the trick?
No, due to the 15 digit limit for numbers in Excel
Mr. Orrell
yes you're right!
no matter how long the number - in the cell it will only show 15 digits - the rest being zeroes...
thanks
