This project has moved. For the latest updates, please go here.

Suppress "... cell is formatted as text or preceded by an apostrophe..." in Excel?

Nov 16, 2011 at 1:02 PM
Is it possible to store a numeric value for example 2871200000000 in a cell without getting the Green Arrow tooltip warning in Excel telling me "the number in this cell is formatted as text or preceded by an apostrophe"?

 

In my example below I elaborate with four tricky values 02871200E02, 2871200E02, 2871200000000, 40%. When running the code as is you'll see the green arrows in Excel, but if you uncomment the commented rows the excel.interop code will do the trick. 

 

Is there a way to do it "ClosedXML native"?


		Dim wb As XLWorkbook = New XLWorkbook()
		wb.Worksheets.Add("One")

		wb.Worksheets(0).Cell(1, 4).SetValue("02871200E02")
		wb.Worksheets(0).Cell(1, 4).Style.NumberFormat.Format = "@"

		wb.Worksheets(0).Cell(2, 4).SetValue("2871200E02")
		wb.Worksheets(0).Cell(2, 4).Style.NumberFormat.Format = "@"

		wb.Worksheets(0).Cell(3, 4).SetValue("2871200000000")
		wb.Worksheets(0).Cell(3, 4).Style.NumberFormat.Format = "@"

		wb.Worksheets(0).Cell(4, 4).SetValue("40%")
		wb.Worksheets(0).Cell(4, 4).Style.NumberFormat.Format = "@"

		Dim file As String = "C:\TEMP\" + Now.ToString("yyyyMMdd hhmmss") + ".xlsx"
		wb.SaveAs(file)		

		'Dim xl As New Excel.Application
		'Dim xlw As Excel.Workbook

		'xlw = xl.Workbooks.Open(file)
		'Dim ws As Excel.Worksheet = xlw.Worksheets(1)

		'Dim lastCell As String = ws.Range("D1").SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Address

		'For Each cell As Object In ws.Range("D1:" & lastCell)
		'	For Each enu As Integer In System.[Enum].GetValues(GetType(Excel.XlErrorChecks))
		'		cell.Errors(enu).ignore = True
		'	Next
		'Next

		'xlw.Save()
		'xlw.Close()
		'xl.Quit()




Coordinator
Nov 16, 2011 at 4:54 PM

wb.Worksheets(0).Cell(3, 4).SetValue(2871200000000)

or

wb.Worksheets(0).Cell(3, 4).Value = 2871200000000

Nov 15, 2013 at 3:00 PM
How do you suppress the green arrow when you add the worksheet from a datatable, such as:
Workbook.Worksheets.Add(dataTable, "Some Name");
Jun 4, 2014 at 8:04 PM
I am using Excel 2007. I know if you go to Excel options -> Formulas -> Error Checking Rules, and uncheck "Numbers formatted as text or preceded by an apostrophe" that removes it for all instances of excel that you open. I don't think that's an option you can change per workbook (I could be wrong), at least I didn't see it in the ClosedXML.

However you can follow the instructions here to make sure that the number you want is displayed as text