Display blank value for value starts with 0 with decimal in Excel

Currently,
If value is 0.4 then it was showing 0
if value is -0.4 then (0).

Whenever the value starts with 0 or (0) irrespective of it’s decimal value, it should display as blank. Number format which i’m using here is 38.

Is it possible to do round off calculation in Aspose cells?


is there any way i can do this?

If value is 0.5 rounded off to 1 but for value like 0 to 0.4 is 0 but i need this to display as blank.

same way negative -0.1 to -0.4 to blank values.


This Topic is created by Amjad_Sahi using Email to Topic tool.

@aravin,

I tried a few variations using custom formatting strings in MS Excel manually but to no avail. How could you accomplish the task using Format Cells… dialog in MS Excel manually? Please implement your desired task in MS Excel and save the file to provide us, we will check on how to do it via Aspose.Cells APIs.

Hi Amjad,

I tried like this =ROUND(0.01,0) to get the blank values. Not sure this is the right way to do it.

Thanks,
Aravinth

@aravin,

This is not working.

As requested earlier, if you could implement your task in MS Excel, you may do the same with Aspose.Cells APIs. So, kindly do the needful and provide us your sample file.

@aravin,
For your requirements, you may try conditional formatting. Code example:

        ConditionalFormattingCollection cfc = sheet.ConditionalFormattings;
        FormatConditionCollection fcc = cfc[cfc.Add()];
        fcc.AddArea(CellArea.CreateCellArea(0, 0, 0, 0));
        fcc.AddCondition(FormatConditionType.CellValue, OperatorType.Between, "-0.999999999999999", "0.999999999999999"); //here user may change the condition according to his requirement
       fcc[0].Style.Custom = "\"\";\"\";\"\"";
        sheet.Cells[0, 0].PutValue(-0.5);
        workbook.Save("test.xlsx"); 

Let us know your feedback.

@aravin,

See the following sample code to accomplish your exact task for your requirements:
e.g
Sample code:

Workbook workbook = new Workbook();
            Worksheet sheet = workbook.Worksheets[0];
        ConditionalFormattingCollection cfc = sheet.ConditionalFormattings;
        FormatConditionCollection fcc = cfc[cfc.Add()];
        fcc.AddArea(CellArea.CreateCellArea(0, 0, 0, 0));
        fcc.AddCondition(FormatConditionType.CellValue, OperatorType.Between, "-0.499999999999999", "0.499999999999999"); //you may change the condition according to your requirements
       fcc[0].Style.Custom = "\"\";\"\";\"\"";
        sheet.Cells[0, 0].PutValue(-0.5);
        workbook.Save("e:\\test2\\out1.xlsx") ;

Open the output file and put some values (e.g “-0.423”, “-0.3”, “0.4”, etc.), you will see it displays blank when you enter such values.

Hope, this helps a bit.