When I paste a long number, Excel turns it into scientific notation and changes numbers.

%3CLINGO-SUB%20id%3D%22lingo-sub-78482%22%20slang%3D%22en-US%22%3EWhen%20I%20paste%20a%20long%20number%2C%20Excel%20turns%20it%20into%20scientific%20notation%20and%20changes%20numbers.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-78482%22%20slang%3D%22en-US%22%3E%3CP%3EHere's%20an%20example.%26nbsp%3B%20I%20have%20a%20long%20number%20(it's%20from%20a%20license%20plate%2Fbar%20code)%3A%3C%2FP%3E%3CP%3E00100018320523710862%3C%2FP%3E%3CP%3EWhether%20I%20use%20my%20bar%20code%20software's%20export-excel%20function%2C%20or%20PASTE%20the%20number%20directly%2C%20here%20is%20what%20I%20get%3A%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3E%3CP%3E1.00018E%2B17%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3EIf%20I%20then%20format%20the%20cell%20as%20a%20number%2C%20I%20get%3A%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3E%3CP%3E100018320523710000%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3EAs%20you%20can%20see%2C%20I%20have%20lost%20the%20'0862'%20at%20the%20end%20of%20my%20number.%26nbsp%3B%20Any%20ideas%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-78482%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-275773%22%20slang%3D%22en-US%22%3ERe%3A%20When%20I%20paste%20a%20long%20number%2C%20Excel%20turns%20it%20into%20scientific%20notation%20and%20changes%20numbers.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-275773%22%20slang%3D%22en-US%22%3E%3CP%3EI%20was%20having%20this%20same%20problem%3A%2020%20digit%20number%20was%20getting%2000000s%20at%20the%20end%20or%20being%20stored%20in%20scientific%20notation.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20work%20around%3A%20format%20the%20cell%20as%20text%20and%20add%20a%20'%20in%20front%20of%20the%20number%20string.%26nbsp%3B%20The%20cell%20will%20not%20display%20the%20'%20before%20the%20number%20and%20will%20display%20only%20the%20full%20number%20without%20zeros%20at%20the%20end.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEx.%26nbsp%3B%20123456789123456789%20was%20showing%20up%20as%20123456789123456000%20when%20formatted%20as%20anumber%20and%20showing%20in%20scientific%20notation%20when%20formatted%20as%20text.%26nbsp%3B%20Changed%20cell%20formatting%20to%20text%20and%20entered%20'%3CSPAN%3E123456789123456789%20as%20the%20cell%20value.%26nbsp%3B%20Cell%20displays%26nbsp%3B123456789123456789%20accurately%20afterwards.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-236059%22%20slang%3D%22en-US%22%3ERe%3A%20When%20I%20paste%20a%20long%20number%2C%20Excel%20turns%20it%20into%20scientific%20notation%20and%20changes%20numbers.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-236059%22%20slang%3D%22en-US%22%3E%3CP%3EMr.%20Orrell%3C%2FP%3E%3CP%3Eyes%20you're%20right!%3C%2FP%3E%3CP%3Eno%20matter%20how%20long%20the%20number%20-%20in%20the%20cell%20it%20will%20only%20show%2015%20digits%20-%20the%20rest%20being%20zeroes...%3C%2FP%3E%3CP%3Ethanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-235930%22%20slang%3D%22en-US%22%3ERe%3A%20When%20I%20paste%20a%20long%20number%2C%20Excel%20turns%20it%20into%20scientific%20notation%20and%20changes%20numbers.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-235930%22%20slang%3D%22en-US%22%3E%3CP%3ENo%2C%20due%20to%20the%2015%20digit%20limit%20for%20numbers%20in%20Excel%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-235082%22%20slang%3D%22en-US%22%3ERe%3A%20When%20I%20paste%20a%20long%20number%2C%20Excel%20turns%20it%20into%20scientific%20notation%20and%20changes%20numbers.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-235082%22%20slang%3D%22en-US%22%3Ewould%20pre-formatting%20the%20column%20or%20row%20to%20%22Number%22%20do%20the%20trick%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-234904%22%20slang%3D%22en-US%22%3ERe%3A%20When%20I%20paste%20a%20long%20number%2C%20Excel%20turns%20it%20into%20scientific%20notation%20and%20changes%20numbers.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-234904%22%20slang%3D%22en-US%22%3E%3CP%3EIn%20my%20experience%2C%20once%20the%20number%20is%20converted%2C%20you've%20lost%20the%20original%20number.%26nbsp%3B%20If%20the%20cell%20is%20formatted%20as%20text%20prior%20to%20entering%20the%20number%2C%20you%20will%20retain%20the%20original%20value.%26nbsp%3B%20If%20you%20have%20an%20existing%20.CSV%20file%2C%20you%20can%20use%20the%20Text%20Import%20Wizard%20(Data%26gt%3BGet%20External%20Data%26gt%3BFrom%20Text)%20to%20open%20the%20file%2C%20and%20set%20the%20column%20format%20as%20text%20during%20the%20import%20process%2C%20and%20it%20should%20retain%20the%20values%20as%20well.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-223301%22%20slang%3D%22en-US%22%3ERe%3A%20When%20I%20paste%20a%20long%20number%2C%20Excel%20turns%20it%20into%20scientific%20notation%20and%20changes%20numbers.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-223301%22%20slang%3D%22en-US%22%3EThis%20does%20not%20work.%20When%20you%20change%20the%20number%20to%20text%2C%20you%20still%20get%20scientific%20notation.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-78637%22%20slang%3D%22en-US%22%3ERe%3A%20When%20I%20paste%20a%20long%20number%2C%20Excel%20turns%20it%20into%20scientific%20notation%20and%20changes%20numbers.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-78637%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20might%20also%20like%20to%20add%20your%20vote%20here%3C%2FP%3E%3CP%3E%3CA%20title%3D%22https%3A%2F%2Fexcel.uservoice.com%2Fforums%2F304921-excel-for-windows-desktop-application%2Fsuggestions%2F10374741-stop-excel-from-changing-large-numbers-actually%22%20href%3D%22https%3A%2F%2Fexcel.uservoice.com%2Fforums%2F304921-excel-for-windows-desktop-application%2Fsuggestions%2F10374741-stop-excel-from-changing-large-numbers-actually%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fexcel.uservoice.com%2Fforums%2F304921-excel-for-windows-desktop-application%2Fsuggestions%2F10374741-stop-excel-from-changing-large-numbers-actually%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-78539%22%20slang%3D%22en-US%22%3ERe%3A%20When%20I%20paste%20a%20long%20number%2C%20Excel%20turns%20it%20into%20scientific%20notation%20and%20changes%20numbers.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-78539%22%20slang%3D%22en-US%22%3E%3CP%3EThat's%20by%20design%2C%20you%20may%20see%20up%20to%2011%20digits%2C%20see%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Fhelp%2F2643223%2Flong-numbers-are-displayed-incorrectly-in-excel%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Fhelp%2F2643223%2Flong-numbers-are-displayed-incorrectly-in-excel%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-78499%22%20slang%3D%22en-US%22%3ERE%3A%20When%20I%20paste%20a%20long%20number%2C%20Excel%20turns%20it%20into%20scientific%20notation%20and%20changes%20numbers.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-78499%22%20slang%3D%22en-US%22%3EI%20also%20have%20the%20same%20problem%20and%20tried%20all%20the%20possible%20formatting%20and%20never%20shows%20the%20original%20number.%20How%20do%20you%20get%20the%20money%20back%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-78489%22%20slang%3D%22en-US%22%3ERe%3A%20When%20I%20paste%20a%20long%20number%2C%20Excel%20turns%20it%20into%20scientific%20notation%20and%20changes%20numbers.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-78489%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Keith%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExcel%20can%20only%20operate%20with%2015%20digits.%20Any%20digits%20beyond%20will%20be%20changed%20to%200.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EChange%20the%20number%20format%20to%20%3CSTRONG%3EText%3C%2FSTRONG%3E.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1088217%22%20slang%3D%22en-US%22%3ERe%3A%20When%20I%20paste%20a%20long%20number%2C%20Excel%20turns%20it%20into%20scientific%20notation%20and%20changes%20numbers.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1088217%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ewhen%20you%20will%20save%20in%20database%20that%20time%20convert%20value%20into%20toString()%20like%3C%2FP%3E%3CP%3EConvert.toString(%3CSPAN%3E00100018320523710862%3C%2FSPAN%3E)%3C%2FP%3E%3CP%3Ethen%20it%20will%20save%20original%20value%20in%20database.%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F62768%22%20target%3D%22_blank%22%3E%40Keith%20Kargl%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1437706%22%20slang%3D%22en-US%22%3ERe%3A%20When%20I%20paste%20a%20long%20number%2C%20Excel%20turns%20it%20into%20scientific%20notation%20and%20changes%20numbers.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1437706%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F62768%22%20target%3D%22_blank%22%3E%40Keith%20Kargl%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20need%20detailed%20instructions%2C%20please%20see%20attached%2C%20in%20case%20you%20are%20familiar%20with%20the%20functionality%20in%20XL%20you%20will%20need%20to%3A%3C%2FP%3E%3CP%3E1-%20copy%20your%20long%20numbers%20into%20a%20.txt%20file.%20Save%20it%20somewhere%20on%20your%20machine%20(or%20in%20any%20cloud%20you%20can%20access%20it)%3C%2FP%3E%3CP%3E2-%20open%20XL%2C%20select%20from%20the%20%22Data%22%20menu%20the%20option%20%22From%20Text%2FCSV%22.%20Search%20for%20your%20file%20and%20click%20%22Import%22%3C%2FP%3E%3CP%3E3-%20don't%20click%20on%20%22Load%22%2C%20but%20choose%20%22Transform%20Data%22%2C%20validate%20your%20Data%20Type%20states%20%22Text%22%20and%20then%20click%20on%20%22Close%20%26amp%3B%20Load%22%3C%2FP%3E%3CP%3E4-%20select%20all%20the%20cells%20that%20you%20need%20from%20the%20list%2C%20and%20do%20a%20Copy%2F%20Paste%20Values.%3C%2FP%3E%3CP%3EDONE!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETS%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

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?

12 Replies
Highlighted

Hello Keith

 

Excel can only operate with 15 digits. Any digits beyond will be changed to 0.

 

Change the number format to Text.

Highlighted
I also have the same problem and tried all the possible formatting and never shows the original number. How do you get the money back?
Highlighted
This does not work. When you change the number to text, you still get scientific notation.
Highlighted

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.

Highlighted
would pre-formatting the column or row to "Number" do the trick?
Highlighted

No, due to the 15 digit limit for numbers in Excel

Highlighted

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

Highlighted

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.

Highlighted

 

when you will save in database that time convert value into toString() like

Convert.toString(00100018320523710862)

then it will save original value in database.

@Keith Kargl 

Highlighted

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