format big numbers

[nlyrk0]

why does my excel format this way, since the formatting when I open the text is different?

1.PNG

2.PNG

I already tried all kinds of formatting there to test.

[xladept]

If you want to see all the digits, you'll need to format those fields as text.

Something like this in a larger scale:

Sub FormatBigNos(): Dim S As String
ActiveCell.NumberFormat = "@"
S = CStr(ActiveCell): ActiveCell = S
End Sub

[joeu2004]

@nlyrk0.... Format the cells as Number with zero decimal places.

It appears that you are opening or importing a CSV file. In that case, Excel assigns the General format to the cells. With the General format, numbers with more than 11 digits are displayed in Scientific form (e.g. 1.23457E+14). But that is just how they appear, as is true for all formatting options.

Since your numbers have 13 or 14 digits, no precision is lost. So you can just change the format to Number with zero decimal places.

However, if you ever see E+15 or larger, precision is lost.

In that case, you must be sure that Excel interprets the data as text when the data is input.

Changing the format afterwards does not correct the problem. Digits to the right of the first 15 digits are replaced with zeros.

In order to ensure that Excel interprets the data as text when the data is input, import the CSV file instead of opening it directly. And in the last menu of the import wizard, change the column format to Text.

IMHO, this is a good thing to do even if the data has 15 or fewer digits. The point is: they are "identifiers", not "numbers". They are not to be treated as numbers, even if they look like numbers.

[xladept]

You'e welcome and thanks for the rep!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.