How to work with numbers containing more than 15 digits in Excel

[swapnilpradhan2688]

I have a 16 digit number 2019016400766922. When I paste it as Text it shows 2.01902E+15 after this when i double click on dis it gives me 2019016400766920. Last digit 2 becomes 0. kindly help. thank you

 

[Joe4]

Convert it to text, and work with it like a string, not a number.

 

[swapnilpradhan2688]

i did as you have mentioned before posting the query but it is not giving me desired answer

 

[Joe4]

It all depends on how exactly you enter it.
One way to make sure it gets entered as text is to first type in a single quote, then the value, i.e.
' 2019016400766922
Then it will keep all 16 digits, and won't display the '.

 

[swapnilpradhan2688]

thank you for the solution
but if we have more data than what should be the solution as it will be difficult to put " ' " in each cell.

 

[Joe4]

How exactly is the data being entered into Excel?
Are you importing it, or entering it manually?
What column(s) are these long number strings being entered into?

 

[swapnilpradhan2688]

i have the data in word 2013. first i convert the column in excel to text and than copy the whole in word and paste it in excel.

 

[Joe4]

I think the copying may be an issue. I don't know if it is possible to get it to maintain the text format copying that way.
I don't deal too much copying/importing data from Word to Excel. Perhaps one of these methods will work better: 3 Ways To Import Data From MS Word To Excel.
You would have to try it out and see if any of them work better.

 

[joeu2004]

i have the data in word 2013. first i convert the column in excel to text and than copy the whole in word and paste it in excel.

The following works for me in Excel 2020, assuming the MSWord text is unstructured. That is, it is just plain text in an MSWord doc.

See the image below.

1. Select and copy the column of text in MSWord.
2. In Excel, format the destination columns or cells as Text.
3. Select the first target cell, right-click, and click Paste Options: Match Destination Formatting.

(Note: That might also work for structured MSWord data; that is, the data is in a table object. I just didn't try it.)

Column A shows the result after copy-and-paste-match-format into A2.

-----
PS.... Even though the data appears as text with no loss of accuracy, you still cannot reference the cells in an arithmetic expression with any expectation of retaining that accuracy.

Whenever Excel interprets text as a number, it only interprets the first 15 significant digits, replacing any digits to the right with zeros.

 

[joeu2004]

The following works for me in Excel 2020

Stoopid typo! I meant Excel 2010.

(I wish this site didn't have a time limit on edits.)