Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom Number Format for text
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom Number Format for text
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom Number Format for text
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 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom Number Format for text
"Cortez" 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)) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom Number Format for text
On Fri, 7 Oct 2011 13:39:15 -0700 (PDT), Cortez wrote:
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. Unfortunately, there is not. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom Number Format for text
On Fri, 7 Oct 2011 14:40:33 -0700, "joeu2004" 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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom Number Format for text
"Ron Rosenfeld" wrote:
On Fri, 7 Oct 2011 14:40:33 -0700, "joeu2004" 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? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom Number Format for text
On Fri, 7 Oct 2011 19:18:10 -0700, "joeu2004" wrote:
"Ron Rosenfeld" wrote: On Fri, 7 Oct 2011 14:40:33 -0700, "joeu2004" 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" |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom Number Format for text
"Ron Rosenfeld" wrote:
On Fri, 7 Oct 2011 19:18:10 -0700, "joeu2004" wrote: "Ron Rosenfeld" wrote: On Fri, 7 Oct 2011 14:40:33 -0700, "joeu2004" 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. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom Number Format for text
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" wrote in message ... "Ron Rosenfeld" wrote: On Fri, 7 Oct 2011 19:18:10 -0700, "joeu2004" wrote: "Ron Rosenfeld" wrote: On Fri, 7 Oct 2011 14:40:33 -0700, "joeu2004" 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. |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom Number Format for text
On Sat, 8 Oct 2011 10:03:54 -0700, "joeu2004" wrote:
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. I was not trying to be a "smart-ass" but I apologize since it apparently came across that way. 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, and when you posted subsequently that it could not be done in XL2003, I wondered if my memory was faulty about that feature in XL2003, or if there were some other issue. Since your posts are generally well thought out, complete and quite detailed, I wondered what I was missing. It never occurred to me that you were unaware of that technique. |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom Number Format for text
"Ron Rosenfeld" wrote:
On Sat, 8 Oct 2011 10:03:54 -0700, "joeu2004" 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. |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom Number Format for text
On Sat, 8 Oct 2011 12:36:07 -0700, "joeu2004" wrote:
Instead, I addressed my comments to his original request for a "custom [number] format" per se. Hence my misunderstanding when you wrote "CF" and were likely thinking custom number formatting. Again, I apologize for having offended you. 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, I agree. |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom Number Format for text
"Ron Rosenfeld" wrote:
I apologize for having offended you. Okay. I'm having a "bad hair" day ;-). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Concatenate numbers and text while holding a custom number format. | Excel Worksheet Functions | |||
Custom Number Format with Text as formula | Excel Discussion (Misc queries) | |||
custom number and random text format | Excel Discussion (Misc queries) | |||
Custom Number Format Text | Excel Discussion (Misc queries) | |||
change custom format number to text | Excel Discussion (Misc queries) |