Number format

[sujittalukde]

Want to change number format so that 0 (zero) figure will display ?NIL? but the excel should take it as number 0 (Zero) & not text NIL for calculation purpose

[Haldun]

Change number format with following...
0;-0;"NIL"

Please note that ';' sign depends your regional settings.

[jammer6_9]

This option is if you want VBA. I don't know if I got you right? Assuming that "0" is in cell "A5"...


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("a5").Value = "0" Then
Range("a5").Value = "NIL"
End If
End Sub



or In vice-versa

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("a5").Value = "NIL" Then
Range("a5").Value = "0"
End If
End Sub

[sujittalukde]

I've not checked the macros but the format provided by Haldun is working. Thanks to all

[Simon Lloyd]

Custom format the cell with [=0]"Nil";General