Have Geek Brain

Opening HTML table in Excel - numbers change

so I have this HTML table with a bunch of big numbers in it that I want to open in Excel 2007 ... you can follow at home:

<table>
<tr>
    <td>this is a big number</td>
</tr><tr>
    <td>1111111</td>
</tr><tr>
    <td>2335322864</td>
</tr><tr>
    <td>23353228641</td>
</tr><tr>
    <td>233532286418</td>
</tr><tr>
    <td>2335322864187</td>
</tr><tr>
    <td>23353228641877</td>
</tr><tr>
    <td>233532286418777</td>
</tr><tr>
    <td>2335322864187774</td>
</tr><tr>
    <td>23353228641877745</td>
</tr><tr>
    <td>233532286418777456</td>
</tr><tr>
    <td>2335322864187774562</td>
</tr><tr>
    <td>23353228641877745623</td>
</tr><tr>
    <td>233532286418777456238</td>
</tr>

when I open this file in Excel it starts converting those numbers to scientific notation when they get more than 10 digits in length ... and while doing so it starts changing the actual number and replaces least significant digits with zeros

how can i tell Excel not to do this?

+2


source to share


2 answers


Make them strings, for example by placing quotes around them (or in front). Not perfect, but you can replace all quotes with nothing later.



+2


source


In Excel 2007, it works great. I just had to change NumberFormat

to 0000000000000000000000

.

In general, trying to get Excel to interpret the Html file is fragile. You should use XML spreadsheets instead, or create a real binary Excel file using something like NPOI . There is a facility in XML table and NPOI to declare the format of the number.



ADDITION . It should also be noted that you are reaching the upper limits of Excel numeric values. From their documentation, the highest digital precision is 15 digits. This means that anything more than that must be converted to scientific notation if it is to be used in calculations.

For details, see Calculation Specifications and Limits .

+2


source






More articles:


All Articles