Trailing Zeros Problem. Need Help Badly

[vaneeshjha]

Hi,

I had a excel sheet sent over to me it had account number of 16 digits

something like this

[TABLE="width: 118"]
<tbody>[TR]
[TD="width: 118"]8342561027735067[/TD]
[/TR]
</tbody>[/TABLE]

but when i opened excel sheet all the numbers were converted to something like this

[TABLE="width: 118"]
<tbody>[TR]
[TD="width: 118, align: right"]8.14731E+15[/TD]
[/TR]
</tbody>[/TABLE]

When i tried converting it into number all the digits had zeros in the end and account numbers lost its significance

i.e. something like this
8342561027735060

zero in the end. i have been searching a lot couldn't find anything. Maybe you guys can help me out. The account numbers are very critical and i am the only user who has the copy. The machine on which excel sheet was composed that user has already deleted it.

 

[jim may]

You could Format the range of cells - Format, Custom, 0000000000000000

or the Macro Equivalent of:

Sub Macro1()
Selection.NumberFormat = "0000000000000000"
End Sub

 

[joeu2004]

I had a excel sheet sent over to me it had account number of 16 digits
something like this
[TABLE="width: 118"]
<tbody>[TR]
[TD="width: 118"]8342561027735067[/TD]
[/TR]
</tbody>[/TABLE]
but when i opened excel sheet all the numbers were converted to something like this
[TABLE="width: 118"]
<tbody>[TR]
[TD="width: 118, align: right"]8.14731E+15[/TD]
[/TR]
</tbody>[/TABLE]
When i tried converting it into number all the digits had zeros in the end and account numbers lost its significance i.e. something like this
8342561027735060

zero in the end.

You need to enter account "numbers" as text.

You do not provide sufficient information for us to help you with that.

But my guess is: the "excel sheet" that some sent you is actually a CSV file (suffix "csv", not "xls", "xlsx", etc). Otherwise, there would be no conversion if you simply open a true Excel file (suffix "xls", "xlsx", etc).

In that case, instead of opening the file directly, you should start Excel (click on Excel program icon, not the filie icon), and import the data file by clicking on Data, Get External Data, From Text. Then following the Text Import Wizard dialog. When you get to the last menu (step 3 of 3), select the column with the account "numbers", then select Text under "Column data format".

Explanation....

When you input numbers into Excel, either manually or by reading a file, Excel interprets only the first 15 significant digits, replacing any subsequent digits with zeros.

Once that happens, there is nothing you can do to recover the replaced digits.

The notation 8.14731E+15 is called Scientific format. It is how Excel presents large (and very small) numbers when the cell is formatted as General. This just affects the appearance of numbers.

When you open a CSV file directly in Excel, most cells are formatted as General because, by definition, a CSV file does not include the original format of the cells.

Some notable examples... If the data looks like a date or time, Excel formats the cell as Date or Time. If the data looks like a percentage, Excel formats the cell as Percent. There might other exceptions. But the point is: Excel interprets the data as numeric one way or another, if looks numeric.

 

[Michael M]

And...Excel has a 15 digit limit !