Custom Format - Single Quote as thousand seperator

Aspose.Cells Product Family
1

Hi,

I just want to show thousand separator value using single quote (').

I tried with following custom format in Excel.,

#'##0.00 -> It works on 1'000.00 , 10'000.00 , 100'000.00 But not on this 1000'000.00

Any ideas how to done this.,?

Thanks

2

Hi,


Thank you for contacting Aspose support.

Please use the custom pattern as #’##0.00 for numbers up to 999,999, #’###’##0.00 for numbers from 1,000,000 to 999,999,999 and #’###’###’##0.00 for numbers from 1,000,000,000 to 999,999,999,999. Please check following piece of code to achieve the same using Aspose.Cells for .NET API.

C#

Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
Cells cells = sheet.Cells;
Cell A1 = cells[“A1”];
A1.PutValue(“999999999999.99”, true);
Style style = A1.GetStyle();
style.Custom = “#’###’###’##0.00”;
A1.SetStyle(style);
sheet.AutoFitColumns();
workbook.Save(@“output.xlsx”);
3

Hi

Thanks for your reply.,

But if i put value at A1 cell as 100., The result is ''100.00

The value is leading with single quotes.,

Is there any other way to achieve this.,?
4

Hi,


As I narrated earlier, you will have different pattern for different length of values. In this case, the value is shorter than a thousand so you will adjust the pattern accordingly. By the way, best way to deal with such scenario is to either change your system’s regional settings to a region where single quote is a thousand separator (this will apply to all applications) or override the settings in Excel application only while using the File > Options > Advance > Editing Options > Thousand separator. You have to deselect “Use system separators” option in order to override these settings.
5

Hi.,

Thanks for your reply…

Really a nice support by you., :slight_smile:


6

Hi,


You are welcome. Please feel free to contact us back in case you need our further assistance with Aspose APIs.