"The number in this cell is formatted as text or preceded by an apostrophe" error

[Vidyasagar612]

Hi Team,



I have a Range object and I am using custom style to one column using below.

styleObject.Custom = “#,##0.00000;#,##0.00000”



Dim styleObject As Style = workbook.Styles(workbook.Styles.Add())

styleObject.Font.Name = “Arial”

styleObject.Font.Size = 8



Dim flag As New StyleFlag()

flag.NumberFormat = True



cellRange.ApplyStyle(styleObject, flag)

I am getting “The number in this cell is formatted as text or preceded by an apostrophe”. How to resolve please help.



Thanks in advance.

[Amjad_Sahi]

Hi,

Well, I am afraid, your value of the range cell(s) (in the sheet) is a string value not a number value. So, when you apply your desired custom formatting to the cells, it is not applied and rather MS Excel prompts you the tool tip “The number in this cell is formatted as text or preceded by an apostrophe”. Please make sure that the cells’ value is numeric and not string. By the way, if you are using Cell.PutValue(string) in your code to insert the value into the cells dynamically, please try using Cell.PutValue(string,true) instead. Aspose.Cells will convert the string value to a number value (if appropriate).

Thank you.

[Vidyasagar612]

I defined the field as an integer and now I am getting this warning. Is there a way to handle this using styleobject?

[Amjad_Sahi]

Hi,

Well, you cannot apply formatting to string fields, so, you got to convert your string values to numeric values first. Your so called numeric cells must have string data. For example when you input the data using e.g., cell.PutValue (“123”), then the inserted value would be string. But if you use cell.PutValue(123) then it is inserted as numeric value. How do you define those cell(s) (in the range)? do you insert or import values dynamically into the cells in the worksheet or you use some template file which has existing data inserted as string, kindly elaborate it more. Could you attach your sample Excel file, we will check it soon.

Thank you.

[Vidyasagar612]

Thanks for your response. I already placed the code which is causing the issue.

Could you please use that for investigation if possible. Also for sub-total reports I am getting extra ’ (single quote)… is there any way to replace with this null while applying custom style.

[Amjad_Sahi]

Hi,

I have tested your scenario/ case using the following sample code and it works absolutely fine and as expected. Please find attached the output file for your reference. You should first refer to my code and run it on your end to get the output file, then you should write your own code accordingly for your needs:

e.g

Sample code:

[C#]

Workbook workbook = new Workbook();

Worksheet worksheet = workbook.Worksheets[0];

//Create range

Range cellRange = worksheet.Cells.CreateRange(“A1:A10”);

for (int i = 0; i < 10; i++)

{

worksheet.Cells[“A” + (i + 1).ToString()].PutValue(123456.24329512 + i);

}

Style styleObject = workbook.CreateStyle();

styleObject.Custom = “#,##0.00000;#,##0.00000”;

styleObject.Font.Name = “Arial”;

styleObject.Font.Size = 8;

StyleFlag flag = new StyleFlag();

flag.NumberFormat = true;

flag.FontName = true;

flag.FontSize = true;

cellRange.ApplyStyle(styleObject, flag);

worksheet.AutoFitColumn(0);

workbook.Save(“e:\test2\outNumbersFormat1.xlsx”);

[VB]

Dim workbook As New Workbook()

Dim worksheet As Worksheet = workbook.Worksheets(0)

'Create range

Dim cellRange As Range = worksheet.Cells.CreateRange(“A1:A10”)

For i As Integer = 0 To 9

worksheet.Cells(“A” & (i + 1).ToString()).PutValue(123456.24329512 + i)

Next i

Dim styleObject As Style = workbook.CreateStyle()

styleObject.Custom = “#,##0.00000;#,##0.00000”

styleObject.Font.Name = “Arial”

styleObject.Font.Size = 8

Dim flag As New StyleFlag()

flag.NumberFormat = True

flag.FontName = True

flag.FontSize = True

cellRange.ApplyStyle(styleObject, flag)

worksheet.AutoFitColumn(0)

workbook.Save(“e:\test2\outNumbersFormat1.xlsx”)

And regarding your other issue “Also for sub-total reports I am getting extra ’ (single quote)…”, please start a new thread with complete details, template files (output file and expected output file (you may create the file with desired results in MS Excel manually)) and sample code, we will check and help you soon there.

Thank you.