HTML to Excel: How Can Tell Excel to Treat Columns as Numbers

Format html table cell numeric value as text for Excel

Answer Taken from:

https://stackoverflow.com/a/4620023/520848

HTML to Excel: How can tell Excel to treat columns as numbers?

Formatting can be applied to the cells for numbers, text, dates, etc.

by adding CSS class:

.num {
  mso-number-format:General;
}
.text{
  mso-number-format:"\@";/*force text*/
}

add the class to the cell

<td class="num">34</td>
<td class="num">17.0</td>
<td class="text">067</td>

Format HTML table cell so that Excel formats as text?

You can apply formatting to the cells for numbers, text, dates, etc.

See my previous answer on this: HTML to Excel: How can tell Excel to treat columns as numbers?

(adjusted snippet)

If you add a CSS Class to your page:

.num {
  mso-number-format:General;
}
.text{
  mso-number-format:"\@";/*force text*/
}

And slap those classes on your TD's, does it work?

<td class="num">34</td>
<td class="num">17.0</td>
<td class="text">067</td>

Forcing numeric values as text on HTML table exporting to excel

Maybe try ="00212704"

Response.Write("<td class='tdsmall' align='left' NOWRAP>=""" & rsPODetail("ITM_ID") & """</td>")

Python df.to_excel() storing numbers as text in excel. How to store as Value?

Consider converting numeric columns to floats since the pd.read_html reads web data as string types (i.e., objects). But before converting to floats, you need to replace hyphens to NaNs:

import pandas as pd
import numpy as np

dfs = pd.read_html('https://www.google.com/finance?q=NASDAQ%3AGOOGL' +
                   '&fstype=ii&ei=9YBMWIiaLo29e83Rr9AM', flavor='html5lib')
xlWriter = pd.ExcelWriter('Output.xlsx', engine='xlsxwriter')
workbook = xlWriter.book

for i, df in enumerate(dfs):
    for col in df.columns[1:]:                  # UPDATE ONLY NUMERIC COLS 
        df.loc[df[col] == '-', col] = np.nan    # REPLACE HYPHEN WITH NaNs
        df[col] = df[col].astype(float)         # CONVERT TO FLOAT   

    df.to_excel(xlWriter, sheet_name='Sheet{}'.format(i))

xlWriter.save()

Importing HTML Table into Excel via clipboard

I recommend the XML Spreadsheet format (the old 2002/2003 one--simpler to generate) over Excel's ability to import HTML.

I've done both and written libraries to export both directly from .NET, and the XML format is definitely less likely to make you lose sleep.

HTML Text with tags to formatted text in an Excel cell

Yes it is possible. In fact let Internet Explorer do the dirty work for you.

MY ASSUMPTIONS

1. I am assuming that the html text is in Cell A1 of Sheet1. You can also use a variable instead.
2. If you have a column full of html values, then simply put the below code in a loop

CODE

Sub Sample()
    Dim Ie As Object
    
    Set Ie = CreateObject("InternetExplorer.Application")
    
    With Ie
        .Visible = False
        
        .Navigate "about:blank"
        
        .document.body.InnerHTML = Sheets("Sheet1").Range("A1").Value
        
        .document.body.createtextrange.execCommand "Copy"
        ActiveSheet.Paste Destination:=Sheets("Sheet1").Range("A1")
        
        .Quit
    End With
End Sub

SNAPSHOT

---

Related Topics