Macro number format thousand separator

[1000qlik]

Dear all,

I am using macros to export some tables to Excel and apply a specific format.

I am struggling with the thousand separator in the number formatting.

It should be a single quote ' and should apply to thousands and millions, if any.

Examples:

1. 222111000 should be 222'111'000
2. 1000 should be 1'000
3. 999 should be 999 (the same)

I have a line code like this:

objExcelDoc.Sheets(sheetName).Columns("C:K").EntireColumn.NumberFormat = "#'##0;(#'##0)"

But the result in this:

1. 222111'000 (missing millions separator)
2. 1'000 (ok)
3. '999 (no single quote should appear)

When I look at the format in Excel, there are "\" automatically added that I think are causing the issue: #\'##0;(#\'##0)

Any help would be greatly appreciated.

Thanks,

Robert

[marcus_sommer]

I think this is only possible with a conditional formating, try this:

[>=1000000]#'###'##0;[>=1000]#'##0;#

- Marcus

[1000qlik]

Hi Marcus,

Thank you for your reply. The conditional formatting you provide works perfectly.

I would now like to format negative values within brackets and still apply the same logic for thousand separators.

Is that possible?

Thank you for your help!

Robert

[jonathandienst]

Hi

You can, but I have found the setting a little flakey. I copy the format string to the clipboard, set the thousands separator and decimal separator (even though the are correct), and then paste the format string back. Hey presto! and it works.

HTH

Jonathan

[1000qlik]

Hi Jonathan,

As long as it works I'm happy

Would you have a sample code that achieves this?

Thanks,

Robert