How to Specify Excel Data Formats in HTML

The Problem

Suppose you have created an HTML page with a table and some data in it, and now you want to work with that data in Excel.

Well, the good news is that Excel can open the HTML page and automatically parse the HTML and convert it into an Excel spreadsheet. Your data table will be automatically populated in Excel cells.

The bad news is that if you have a cell with only numbers in it, for example, a serial number with leading zeros, like this: 000245689. Excel will format it as number and remove the leading zeros. You will then have to add back the leading zeros and format it as text to preserve the leading zeros.

However, not only it's a hassle having to add back the leading zeros, but it's not always possible to know how many leading zeros were in the original number, if you have 100 numbers with leading zeros all with different lengths. You could check the original HTML 1 by 1 but it's very time consuming.

The Solution

Well, it turns out, there is a CSS property that Excel uses to decide how to format the data, it's called:
mso-number-format

We can use this property to tell Excel that the data should be formatted as text and therefore preserving the leading zeros. 

To do that, we assign the CSS style to the HTML table cell with the data we want to format as text.

There are many ways you can assign CSS styles, such as in-line style, use classes, or ids.

Here, as an example, we will use classes to assign the CSS style.

The CSS

.excel-text{
	mso-number-format: "\@";
}

The value "\@" tells Excel to format as text. 

The HTML

Now we just assign the class to the table cell in HTML:

<td class="excel-text">0002586562</td>

Conclusion

Voila! Now Excel will format the cell as text and preserve the leading zeros.

"\@" is not the only value you can specify. In theory, you can specify any valid Excel format strings.

Here are some more examples of Excel data formats you can specify.