Space between few numbers

Hi !
I use Aspose to format and export some excel files.

I Have a problem with space between numbers, when I have decimal numbers each group of number are separate by a space but when it isn’t a decimal I have only space between thousands and hundreds but no one between millions and thousands

Ex :
18000456,34 => 18 000 456,34
18000456 => 18000 456 (actual result)
18000456 => 18 000 456 (wanted result)

Thanks anyone have an idea of my problem

@khloeava,

This issue seems to be related to your locale or regional settings for number formatting when opening the file in MS Excel manually. You might consider setting custom number formatting via Aspose.Cells for the relevant cells in the worksheet through code. If possible, could you kindly share a sample Excel file where your desired formatting has been applied to some cells for the numbers manually, so we can see how you would like the results to appear? Please ensure the file is zipped before attaching it. We will check it soon.

Hi @amjad.sahi !

Thanks for your reply. I follow-up on the subject, I’m working whit Khloeava on this.

We’re already setting this through code. We have two custom settings for 2 different accounting styles. 1 has 2 decimals, 1 is with 0 decimal.We have different cultures, but here’s for french :

2 decimals : “-* # ##0,00" {0}"-;-* # ##0,00" {0}"-;-* "-"??" {0}"-;-@-"
0 decimal : "
-* # ##0" {0}"-;-* # ##0" {0}"-;-* "-"??" {0}"-;-@-”

({0} will be overridden with custom currency symbol)

And here’s how it’s set up in the document :

cellStyle.CultureCustom = GetCustomAccountingStyle(cellStyle.CultureCustom, _exportData.CurrencySymbol)
_worksheet.Cells[styleParams.Cell].SetStyle(cellStyle);

2 decimals is correctly printed : 18 000 456,00
0 decimal is not : 18000 456

Thanks again ! :slight_smile:

@Opus_Team,

Apparently, it looks like MS Excel does not support your desired formatting in your way. Could you please share a sample Excel file where your desired formatting (for 0 decimal) has been applied to some cells for the numbers manually, so the results appear in your way. Please ensure the file is zipped before attaching it. We will review it soon.

Hi @amjad.sahi,

I edited manually an Excel to put our code (replacing the currency and so on) in the cell and it works juste fine (I attached the file).
Upon looking further, when editing the custom cell format after generatign it, here’s the format in the cell : “-* #\ ##0" €"-;-* #\ ##0” €"-;-* “-”??" €"-;-@_-"

Looks like I have some additional quotes that shouldn’t be here. Maybe the " does not escape as well as we want it. I’ll make some more testing but if you have any additional ideas, I’m open to suggestions.

Have a nice day :slight_smile:

tests.zip (6,7 Ko)

@Opus_Team

For separating number value into groups, the separator should be the default number group separator of current locale/region(CultureInfo.NumberFormat.NumberGroupSeparator). If it is your case, you may try to set the custom pattern for style:

style.Custom = "-* #,##0 {0}"; //here {0} should be replaced with the expected symbol. And the default/standard separator ',' will be replaced with the locale-dependent separator such as ' ' automatically when you getting the formatted result

or

style.CultureCustom = "-* # ##0 {0}"; //here {0} should be replaced with the expected symbol, and please make sure there should not be '\' before the space character like "-* #\ ##..."

Hi @johnson.shi !

Sorry for the late answer.
I tried writing in my code just what you said but, when opening the Excel file, I have additional \

Code :

accountingStyle = “-* # ##0 {0}”;

Excel :

-* #\ ##0\ \€

I have no idea how they appear since, when looking just before setting the cellStyle.Custom, I have the correct value.

I’ll continue testing, any idea or intuition is welcome :smiley:

Edit : tried cell.Number = 41. Worked perfectly except I can’t customize currency :confused:

@Opus_Team,

I tested using the following sample code, and it appears to be working well. The resulting Excel file looks good.
e.g.,
Sample code:

Workbook workbook = new Workbook();
Aspose.Cells.Cells cells = workbook.Worksheets[0].Cells;
Aspose.Cells.Cell cell = cells["A1"];
cell.PutValue(298468);
Style style = cell.GetStyle();
style.Custom = "-* # ##0 €";
cell.SetStyle(style);
workbook.Worksheets[0].AutoFitColumn(0);
workbook.Save("e:\\test2\\out1.xlsx");

Kindly find attached the Excel file for your reference. We would appreciate it if you could review the cell formatting in the spreadsheet and confirm whether the formatted value is displayed correctly?
out1.zip (6.0 KB)

Hi @amjad.sahi !

Unfortunately, as I’m not the owner of this thread, I can’t download the zip.
But I put the code snippet into my function and executed it

Workbook workbook = new Workbook();
Aspose.Cells.Cells cells = workbook.Worksheets[0].Cells;
Aspose.Cells.Cell cell = cells[“A1”];
cell.PutValue(1298468);
Style style = cell.GetStyle();
style.Custom = “-* # ##0 €”;
cell.SetStyle(style);
workbook.Worksheets[0].AutoFitColumn(0);
workbook.Save(“C:\Temp\out1.xlsx”);

I just added an additionnal “1” in the value because the issue is with the million separator so we need more than 6 digits numbers (thousand - hundred is ok but million - thousand is not)

With your code (or at least when it’s put inside my function) I have the issue also. I zipped the result if you want to check.
out1.zip (6,0 Ko)

@Opus_Team,

Thanks for the sample file and further details.

You are correct, and I have tested it myself. I identified the issue, it lies with the million separator. While the thousand and hundred separators work fine, the million-thousand separator does not function as expected. I attempted to adjust this manually in MS Excel (I tried some variations for custom strings) but was unsuccessful. Could you kindly explain how this can be achieved manually in MS Excel? If MS Excel supports this formatting, we will replicate it accordingly. Would it be possible for you to share a sample file with your desired formatting (you may even edit your attached Excel file and formulate a string to set which should display your desired output), where a space is used as the million separator? This will help us review and address the issue soon.

Hi @amjad.sahi !

Sorry for the delay.

Using the style you provided (“-* # ##0 €”) but placing it manually in the Excel file is OK. But with Aspose, I get the \ added and the style is KO.

I made a gif where I add it manually and it works :
2025-07-24_12h02_46.gif (2,1 Mo)

Hope this helps :slight_smile:

Have a nice day

@Opus_Team,

Thanks for the GIF image.

Could you please zip and attach a sample Excel file which displays your desired (formatting) results (as per your attached GIF). We will look into it further.