I'd like to build a custom number format where the text format section
contains an if statement. Here is an example to explain what I'd like
to replicate:

#,##0_);(#,##0);;if="null" then ""

The number format would leave treat positive and negative values
normally, would display a blank cell if the value is zero, and would
display a blank cell if the cell value is text "null", otherwise if
the cell value is any other text it would display that text.

1,-2,0,null,X would become 1,-2,,,X

I'm not sure if what I am asking makes sence, so please let me know if
I can clarify my question.

What I want is an example of a custom number format that would do
this. If even possible.

Thanks in advance,
TK

Hi TK,

Am Fri, 7 Oct 2011 13:09:39 -0700 (PDT) schrieb Cortez:

> The number format would leave treat positive and negative values
> normally, would display a blank cell if the value is zero, and would
> display a blank cell if the cell value is text "null", otherwise if
> the cell value is any other text it would display that text.
>
> 1,-2,0,null,X would become 1,-2,,,X

try it with custom number format:
#,##0;-#,##0;;@
For text "null" use the conditional format:
if cell value is text "null" then font color is white


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

That's virtually the same as what I have atm, but was looking for
something more eloquent. I was hoping that there was a similar
conditional operater function for the text section as there is for the
numeric sections where [>100] dictates when to apply the format.

Thanks for the suggestion!

TK

On Oct 7, 3:32*pm, Claus Busch <claus_bu...@t-online.de> wrote:
> Hi TK,
>
> Am Fri, 7 Oct 2011 13:09:39 -0700 (PDT) schrieb Cortez:
>
> > The number format would leave treat positive and negative values
> > normally, would display a blank cell if the value is zero, and would
> > display a blank cell if the cell value is text "null", otherwise if
> > the cell value is any other text it would display that text.
>
> > 1,-2,0,null,X *would become 1,-2,,,X
>
> try it with custom number format:
> #,##0;-#,##0;;@
> For text "null" use the conditional format:
> if cell value is text "null" then font color is white
>
> Regards
> Claus Busch
> --
> Win XP PRof SP2 / Vista Ultimate SP2
> Office 2003 SP2 /2007 Ultimate SP2

"Cortez" <(E-Mail Removed)> wrote:
> I'd like to build a custom number format where the text
> format section contains an if statement. Here is an
> example to explain what I'd like to replicate:
> #,##0_);(#,##0);;if="null" then ""
>
> The number format would leave treat positive and negative
> values normally, would display a blank cell if the value
> is zero, and would display a blank cell if the cell value
> is text "null", otherwise if the cell value is any other
> text it would display that text.
> 1,-2,0,null,X would become 1,-2,,,X

You do not mention the version of Excel that you use.

AFAIK, XL2003 does not have any custom formats or format subtypes for Text.
I am pretty sure the same can be said for XL2007 and later.

Also, you cannot use Conditional Formatting to accomplish this in XL2003.
But I believe CF is more robust in XL2010, perhaps XL2007 as well.
Nonetheless, I don't know if it is robust enough to accomplish this kind of
"formatting". I doubt it.

Usually, you must accomplish this in your formula. For example:

=IF(ISNUMBER(A1),A1,IF(A1="null","",A1))

"Ron Rosenfeld" <(E-Mail Removed)> wrote:
> On Fri, 7 Oct 2011 14:40:33 -0700, "joeu2004" <(E-Mail Removed)> wrote:
>>Also, you cannot use Conditional Formatting to accomplish this in XL2003.
>
> I'm surprised. I thought you could format font color in CF in XL2003.

Who said anything about font color?

"Ron Rosenfeld" <(E-Mail Removed)> wrote:
> On Fri, 7 Oct 2011 19:18:10 -0700, "joeu2004" <(E-Mail Removed)> wrote:
>>"Ron Rosenfeld" <(E-Mail Removed)> wrote:
>>> On Fri, 7 Oct 2011 14:40:33 -0700, "joeu2004" <(E-Mail Removed)> wrote:
>>>>Also, you cannot use Conditional Formatting to accomplish this in
>>>>XL2003.
>>>
>>> I'm surprised. I thought you could format font color in CF in XL2003.
>>
>>Who said anything about font color?
>
> Perhaps I misunderstood. When you wrote:
>
>>Also, you cannot use Conditional Formatting to accomplish this in XL2003.
>
> I thought that by "this" you were referring to the OP's request to:
>
>>display a blank cell if the cell value is text "null"

I think I know where you are going with that. But if you have an "aha!",
why not simply spit it out and show us all how smart you are instead of
being a smart-ass and distorting the comments of others.

Even if "this" had referred to CF in general, I simply overlooked an
arguably clever use of it. Again, you could simply present your idea
instead of casting contentless aspersions on my comments.

FYI, "this" referred to "any custom formats or format subtypes for Text". I
wrote, "But I believe CF is more robust in XL2010, perhaps XL2007 as well",
because I think (IIRC) that someone pointed out elsewhere that XL2010
(and/or XL2007) CF now permits us to specify numeric formats per se
conditionally based on cell values.

I might be wrong about that; I don't have XL2007 or XL2010 to double-check.
Note that I wrote "IIRC". If you have XL2007 and/or XL2010, perhaps you can
comment on that. Ah, "that" refers to the specification of numeric formats
per se in Conditional Formatting.

But as I continued to write, ``Nonetheless, I don't know if it is robust
enough to accomplish this kind of "formatting". I doubt it``. Ah, "it"
refers to the specification of formats per se in Conditional Formatting.
And "this kind of formatting" refers to the OP's request for a "text format"
per se.

Errata.... I wrote:
> Even if "this" had referred to CF in general

I should have written: ``Even if "this" had referred to displaying a blank
cell if the cell value is text "null"``.


----- original message -----

"joeu2004" <(E-Mail Removed)> wrote in message
news:j6pvpr$e30$(E-Mail Removed)...
> "Ron Rosenfeld" <(E-Mail Removed)> wrote:
>> On Fri, 7 Oct 2011 19:18:10 -0700, "joeu2004" <(E-Mail Removed)> wrote:
>>>"Ron Rosenfeld" <(E-Mail Removed)> wrote:
>>>> On Fri, 7 Oct 2011 14:40:33 -0700, "joeu2004" <(E-Mail Removed)> wrote:
>>>>>Also, you cannot use Conditional Formatting to accomplish this in
>>>>>XL2003.
>>>>
>>>> I'm surprised. I thought you could format font color in CF in XL2003.
>>>
>>>Who said anything about font color?
>>
>> Perhaps I misunderstood. When you wrote:
>>
>>>Also, you cannot use Conditional Formatting to accomplish this in XL2003.
>>
>> I thought that by "this" you were referring to the OP's request to:
>>
>>>display a blank cell if the cell value is text "null"
>
> I think I know where you are going with that. But if you have an "aha!",
> why not simply spit it out and show us all how smart you are instead of
> being a smart-ass and distorting the comments of others.
>
> Even if "this" had referred to CF in general, I simply overlooked an
> arguably clever use of it. Again, you could simply present your idea
> instead of casting contentless aspersions on my comments.
>
> FYI, "this" referred to "any custom formats or format subtypes for Text".
> I wrote, "But I believe CF is more robust in XL2010, perhaps XL2007 as
> well", because I think (IIRC) that someone pointed out elsewhere that
> XL2010 (and/or XL2007) CF now permits us to specify numeric formats per se
> conditionally based on cell values.
>
> I might be wrong about that; I don't have XL2007 or XL2010 to
> double-check. Note that I wrote "IIRC". If you have XL2007 and/or XL2010,
> perhaps you can comment on that. Ah, "that" refers to the specification
> of numeric formats per se in Conditional Formatting.
>
> But as I continued to write, ``Nonetheless, I don't know if it is robust
> enough to accomplish this kind of "formatting". I doubt it``. Ah, "it"
> refers to the specification of formats per se in Conditional Formatting.
> And "this kind of formatting" refers to the OP's request for a "text
> format" per se.
>
>

"Ron Rosenfeld" <(E-Mail Removed)> wrote:
> On Sat, 8 Oct 2011 10:03:54 -0700, "joeu2004" <(E-Mail Removed)> wrote:
>>Even if "this" had referred to CF in general, I simply overlooked an
>>arguably clever use of it.
[....]
> The reason I raised the question about using CF was
> because an hour earlier, someone had posted using the
> change of the font color to make the cell appear blank in CF
[....]
> It never occurred to me that you were unaware of that technique.

I am not "unaware" of it. I said I [might have] simply "overlooked" it
(i.e. forgot about it).

But actually, looking back at the discussion to refresh my memory, I see
that:

1. Claus wrote: ``For text "null" use the conditional format: if cell value
is text "null" then font color is white``.

2. And Cortez responded: "That's virtually the same as what I have atm, but
was looking for
something more eloquent. I was hoping that there was a similar conditional
operater function for the text section as there is for the numeric sections
where [>100] dictates when to apply the format".

So why would I reiterate a method that Cortez already rejected?
(Rhetorical.) If I had, I'm sure you would have bleated, "Why are you
repeating a suggestion that Cortez already rejected?".

Instead, I addressed my comments to his original request for a "custom
[number] format" per se. I wanted to make the point that such features
might be version-specific, and if Cortez uses XL2007 and later, he/she
should look at CF for some format enhancements as there are for numeric CFs
(IIRC). But I also set expectations that I did not expect any for text
formats per se.

I posted my comments 3 hours before you offered essentially the same answer
in fewer words.

That's about all I will say on this matter. It is a distraction. I try to
focus my attention and comments on issues that I think will benefit the OP,
occassionally nitpicking others' comments again only when I think it will
benefit the OP.

"Ron Rosenfeld" <(E-Mail Removed)> wrote:
> I apologize for having offended you.

Okay. I'm having a "bad hair" day ;-).