It may be possible, but what are you actually trying to do?



The basic format  is broken into four parts separated by a semicolon.
Postive;Negative;Zero;text

I believe that you are limited to two conditions when creating a
Custom Format.

This works fine for identifying "0" values.

[>=10000000]#\,##\,##\,##0;[=0]"Nil";##,##0

Thanks to all for the help and support. As of now it is confirmed
that this cannot be done Can this be done by a macro.

My requirement is like this:

I have a sheet called "Master" which contains column headers and
rows ie designed as records and fields. THe first column contain the
name of the employees and other column related data for the
employees.

The next sheet contains a sheet named "Computation". This is a
format in which I need the output. The cell  A5 of Computation sheet
contains a drop down list(via DataValidation, List ) which contains
the name of the employees.
Other fields are made with Vlookup formula so the data in the
relevant cells  are those of the presons named in cell A5. So when I
change the name at cell A5, the data are also chages. Now in the
cells for some person may have numbers and for some persons the data
may be 0 (Zero) and this zero data I wnat to present as NIL.

Make your formula something like

=if(Vlookup(A5,Master!A1:P9999,3,false)=0,"Nil",Vlookup(A5,Master!A1:P9999,3
,false))

but this will create a problem while summing the cells.
Say at cell  D10 the formula is = D2+D7+D9 as this formula will treat
the "Nil" as text and not number.

Can you go into detail on how this specific cell  is to be formatted? The
example you gave indicated

[>=10000000]#\,##\,##\,##0;

[>=100000]##\,##\,##0;

##,##0
As far as I can see the format  for 100 thousand to 10 million is the same as
the format for under 100 thousand. You can use a single format and rather
than a condition based format you can use the Positive;Negative;Zero;Text

One other thing, the "\" is used to add special characters to a format.
Excel expects numbers to have commas, so the "\" is not necessary.



So from what I see, you should be able to get away with
#\,##\,##\,##0;;"Nil"

You could use VBA to inspect each cell  involved and set its formatting based
on its value.

Record a macro of yourself setting the various custom  formats. You'll see
statements like

Selection.NumberFormat =
"[>=10000000]#\,##\,##\,##0;[>=100000]##\,##\,##0;##,##0"

i.e. the format  specifier is simply a string. So, you could grab the
worksheet change event and buzz around your cells, determining what format
they should be and changing them when necessary. (You probably should avoid
changing a cell's format to what it is already - it might slow things down.
Just check the current format against what you think it should be and change
it if it doesn't match.)

The format  wanted is 12,34,56,789 which is in use in some countries (sorry,
I forget which). Hence the need for literal commas and ranged custom
formats.

Unfortunately, Excel ignores the number  of digits you put between
non-literal commas in a custom  format and assumes you wanted three, no
matter how many you actually had.

I had noted the double hash, but could not remember where I saw that format
before. From a little more playing, it appears that if you enter "#,#" it
reformats to "#,###" and the commas repeat as needed. So it is not necessary
to create a special format  to handle "999,999,999,999,999" . "#,###" will
do.

As I understand it, the format  wanted is "99,99,99,99,99,99,999" not
"999,999,999,999,999". (I.e. a group of three, but then groups of two.)

I was not suggesting that as an answer, just that the #,### format  will
handle up to that number.



We just need more information on what is required.

I'm not sure I understand you. Excel with its normal custom  formats
with commas in them will divide into groups of THREE digits. The
requirement is for groups of TWO digits (except for the right-most three).
Normal Excel custom formats  (i.e. ones that insert the commas automatically)
will not do what is wanted. This is "Indian format".

skt2114, you might like to look at
www.experts-exchange.com/.../Q_20537683.html which seems to
be offering code to dynamically insert appropriate custom formats based on
cell value. This could be modified to do your Nil, if needed.

If that's not what you want, a Google search for - Indian custom format
excel - will get you lots of discussion on the subject, plus a few solutions
of various types.