Default numeric format in Excel

Question

I have a web app that exports reports to Excel. It does this by sending the HTML of the report table to the clipboard, and then pasting it into Excel. Quick and dirty. And it comes through fairly well. Numbers are formatted as numbers, dates as dates, and so on.

But the reports have negative numbers formatted to use parentheses rather than a minus sign, and when this is pasted into Excel, it changes to a minus sign. You can see this in action simply by typing (200) into Notepad, and then selecting it, copying it, and pasting it into a cell in an Excel spreadsheet. It will come through as -200.

My users would like to have it display as (200). And I can use automation from Javascript to manually format selected cells. But that's slow. Is there any way to get Excel to change its default numeric format?

Not without using some sort of add-in easily. This is one of the problems with the quick and dirty method. :D Save an excel document with the format in column you want it. Now rename extension to .zip. Now open the zip file and see all the elements that denote an .xlsx anymore. within the xl folder there's a styles.xlm which I believe will have a line like `` you would have to somehow update the XML of the end users file to accomplish this (or use API's already developed by 3rd party) which is why I say it's not simple. — xQbert, May 28 '14 at 20:36

score 0 · Accepted Answer · edited May 23 '17 at 11:57

0

If you're pasting HTML-formatted data in the form of a table into Excel, then you can include css styles to control how the cell contents are displayed.

See (e.g.) : http://cosicimiento.blogspot.com/2008/11/styling-excel-cells-with-mso-number.html

VBA example:

Sub Formatting()

    Dim html As String, d As New DataObject

    html = "<table><tr><td>(200)</td></tr>" & _
           "<tr><td style='mso-number-format:\@'>(200)</td></tr></table>"

    d.SetText html
    d.PutInClipboard

End Sub

HTML example: CSS Formatting for Excel Web Query

edited May 23 '17 at 11:57

Community

1
1

answered May 28 '14 at 21:18

Tim Williams

154,628
8
97
125

That is *so* close. It's actually like this: mso-number-format:"\#\,\#\#0\.00_ \;\(\[Red\]\#\,\#\#0\.00\\) " But I'm giving you the answer because I never in a trillion years would have gotten it without your pointer. Thanks. – Lisa Liel May 29 '14 at 21:51

Default numeric format in Excel

1 Answers1

Linked