Numbers automatically changed to scientific notation so can't get last digits

Excel stores numbers using a double precision datatype, which can handle up to about 15 digits. When you convert these numbers as text to real numbers, the digits beyond what double precision can store get lost. To my knowledge, there is no way to recover the lost digits, unless you have stored the numbers as text elsewhere.

Wikipedia article with more than you want to know about how Excel stores and manipulates numbers: https://en.wikipedia.org/wiki/Numeri...icrosoft_Excel

Yes I'd be willing to use VBA code if it would help.

I can have the codes in any format as long as they're readable, although ideally I'd like to be able to copy and paste them if needed. This just involves a few cells. Like A3 to A30. I think the data was copied and pasted off a website, but I'm not sure. They all have a space in front of them.

The only issue is the little green triangles on the top right of the cells and the yellow exclamation point when you click on them that gives the error I described previously. The big problem is they change to scientific notation with the last digits missing when I click on them and try to do anything with them. I have some already where the last digits are all zeros now

All I'm trying to accomplish is being able to read the numbers that were originally entered into the sheet

First check if formatting the cells as Number doesn't do what you want.

Sure. I have it attached here.Example numbers for excel forum 12.14.23.xlsx

If all 20 digits of these numbers are important, then, whatever you do, you will want to be sure that these are always stored as text and never stored as numbers. As I noted above, Excel uses double precision to store numbers, and double precision only retains about 15 significant digits.

In your file, I selected A3:A6 and formatted as text @. Then I could double click or similar the values and they would stay as text. There is nothing in the file to tell me what the last five digits of A4 were, so I have no way to recover those digits.

If I understand what is going on, the key is to never let these numbers stored as text be converted to numbers -- whatever you have to do to make sure that doesn't happen. I'd probably go so far as to suggest that you retain a text file backup just in case something happens to convert these text strings to numbers so you can recover the information.

If the green triangle bothers you, go into Excel options where the "error checking options" are, and deselect the "numbers stored as text error" option.

Does that help?

The values are 20 characters long and seen by Excel as Text. They are formatted as General but have a leading space. If you edit the cell and press Enter, it converts the value to a true numeric (by removing the leading space). Even if you refer to the cell with a simple formula, for example, =A3, it will convert the value to a number. But then it is too long to be represented as a number except using scientific notation.

@MrShorty

Yes. I had no idea that excel would erase numbers that were entered by changing the format. I always thought formatting could be reversed, like when you see a date entered as the numbers behind it and you can go back and forth between them. Hopefully I have those numbers backed up somewhere else.

@TMS

That explains it. Just can't believe it would erase the numbers and limit the characters