Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

combine number formats

  Asked By: Klarissa    Date: Dec 23    Category: MS Office    Views: 1191
  

I am using the following number format in a range of cells:


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

Now I want want to combine the following number format :
0;;"Nil"

That is;
if any cell in the range contains 0 (Zero) value then it should appear
as "Nil" otherwise the number should display in the custom number
format.

How can I combine the two number formats?

Share: 

 

13 Answers Found

 
Answer #1    Answered By: Beatriz Silva     Answered On: Dec 23

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

 
Answer #2    Answered By: Yvonne Watkins     Answered On: Dec 23

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

 
Answer #3    Answered By: Yvette Griffin     Answered On: Dec 23

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.

 
Answer #4    Answered By: Brent Brown     Answered On: Dec 23

Make your formula something like

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

 
Answer #5    Answered By: Arnelle Schmidt     Answered On: Dec 23

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.

 
Answer #6    Answered By: Cleopatra Massri     Answered On: Dec 23

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

 
Answer #7    Answered By: Muntasir Bashara     Answered On: Dec 23

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"

 
Answer #8    Answered By: Cadencia Bernard     Answered On: Dec 23

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.)

 
Answer #9    Answered By: Patty Freeman     Answered On: Dec 23

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.

 
Answer #10    Answered By: Johnathan Nelson     Answered On: Dec 23

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.

 
Answer #11    Answered By: Horia Ahmed     Answered On: Dec 23

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.)

 
Answer #12    Answered By: Sophie Williamson     Answered On: Dec 23

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.

 
Answer #13    Answered By: Hattie Howard     Answered On: Dec 23

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.

 
Didn't find what you were looking for? Find more on combine number formats Or get search suggestion and latest updates.




Tagged: