View Full Version : Number format
sujittalukde
05-21-2007, 05:29 AM
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
05-21-2007, 05:44 AM
Change number format with following...
0;-0;"NIL"
Please note that ';' sign depends your regional settings.
jammer6_9
05-21-2007, 05:46 AM
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
05-21-2007, 05:49 AM
I've not checked the macros but the format provided by Haldun is working. Thanks to all
Simon Lloyd
05-21-2007, 05:51 AM
Custom format the cell with [=0]"Nil";General
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.