(Msg. 2) Posted: Wed May 02, 2007 11:21 am
Post subject: Re: Conditional Formatting question (if cell = 0, wrap cell in quotes) [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
Mo,
You don't need to use conditional formatting: just use a custom number format. From Help:
Replace the format for zeroes with "'#0"', or ""'#0"'," I'm not sure which will geive you
your desired results.
HTH,
Bernie
MS Excel MVP
"Mo2" <Mo2 RemoveThis @discussions.microsoft.com> wrote in message
news:EE807DC7-CF0D-497B-AC29-142D18197E0F@microsoft.com...
> IF Cell value = 0, wrap with quotes
> not exactly quotes.. but with this number format: ""'#0"',"
> wrapped in single quotes, followed by a comma.
>
> (format-cells - costum , to see what it looks like)
>
>
> also, i dont want it to apply to every column (if its possible to do that)
> just A1:A323, B1:B323, E1:E323, G1:G323, I1:I323, K1:K323
>
> thanks in advance
>
>
(Msg. 3) Posted: Wed May 02, 2007 11:33 am
Post subject: Re: Conditional Formatting question (if cell = 0, wrap cell in quotes) [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
Mo,
Well, that didn't work well - the section of help that I copied was a .gif - you will need to open
the attachment.. Sorry.
HTH,
Bernie
MS Excel MVP
"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:O5sa81MjHHA.588@TK2MSFTNGP06.phx.gbl...
> Mo,
>
> You don't need to use conditional formatting: just use a custom number format. From Help:
>
>
>
> Replace the format for zeroes with "'#0"', or ""'#0"'," I'm not sure which will geive you
> your desired results.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Mo2" <Mo2 RemoveThis @discussions.microsoft.com> wrote in message
> news:EE807DC7-CF0D-497B-AC29-142D18197E0F@microsoft.com...
>> IF Cell value = 0, wrap with quotes
>> not exactly quotes.. but with this number format: ""'#0"',"
>> wrapped in single quotes, followed by a comma.
>>
>> (format-cells - costum , to see what it looks like)
>>
>>
>> also, i dont want it to apply to every column (if its possible to do that)
>> just A1:A323, B1:B323, E1:E323, G1:G323, I1:I323, K1:K323
>>
>> thanks in advance
>>
>>
>
>
>
(Msg. 4) Posted: Wed May 02, 2007 11:33 am
Post subject: Re: Conditional Formatting question (if cell = 0, wrap cell in quo [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
u mean theres attachments on this forum? where?
a .gif? i dont see that anywhere either... or a broken link or whatnot
Bernie, the problem is this:
I have a # column, a text column, # column, text column, etc
(and i need them in this particular order, to be copied and pasted into
notepad, for a reason or another)
the cells have this format applied to them:
number column: ""'#0"',"
text columns: ""'@"',"
the problem is, sometimes,
the cells in the TEXT column (which only have a text cell format)
will have a 0 in them. it wont be wrapped in quotes and such:(
so my choices (as far as i know) are either
1) find a way to apply BOTH a text AND number cell format to all cells
2) a format which applies to any cell, regardless of text/number value.
(cell range A1:R323)
3) Conditional formatting for 0's
(seems the easiest/most possible method)
i'm not sure what other options i have atm
"Bernie Deitrick" wrote:
> Mo,
>
> Well, that didn't work well - the section of help that I copied was a .gif - you will need to open
> the attachment.. Sorry.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
> news:O5sa81MjHHA.588@TK2MSFTNGP06.phx.gbl...
> > Mo,
> >
> > You don't need to use conditional formatting: just use a custom number format. From Help:
> >
> >
> >
> > Replace the format for zeroes with "'#0"', or ""'#0"'," I'm not sure which will geive you
> > your desired results.
> >
> > HTH,
> > Bernie
> > MS Excel MVP
> >
> >
> > "Mo2" <Mo2.RemoveThis@discussions.microsoft.com> wrote in message
> > news:EE807DC7-CF0D-497B-AC29-142D18197E0F@microsoft.com...
> >> IF Cell value = 0, wrap with quotes
> >> not exactly quotes.. but with this number format: ""'#0"',"
> >> wrapped in single quotes, followed by a comma.
> >>
> >> (format-cells - costum , to see what it looks like)
> >>
> >>
> >> also, i dont want it to apply to every column (if its possible to do that)
> >> just A1:A323, B1:B323, E1:E323, G1:G323, I1:I323, K1:K323
> >>
> >> thanks in advance
> >>
> >>
> >
> >
> >
>
>
>
(Msg. 5) Posted: Wed May 02, 2007 2:10 pm
Post subject: Re: Conditional Formatting question (if cell = 0, wrap cell in quo [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
Mo,
Custom formats have four sections: format for values greater than zero, format for values less than
zero, formats for 0 values, and formats for string values, separated by ;'s. So:
#.00;[Red]#.00;"'0"',;'@',
will do something along the lines of what you want.... Not sure how you want text to appear. Just
play with the strings in the third and fourth positions.
HTH,
Bernie
MS Excel MVP
"Mo2" <Mo2.DeleteThis@discussions.microsoft.com> wrote in message
news:2ED38465-D12F-4C45-972A-560C06C94891@microsoft.com...
>u mean theres attachments on this forum? where?
> a .gif? i dont see that anywhere either... or a broken link or whatnot
>
>
> Bernie, the problem is this:
> I have a # column, a text column, # column, text column, etc
> (and i need them in this particular order, to be copied and pasted into
> notepad, for a reason or another)
>
> the cells have this format applied to them:
> number column: ""'#0"',"
> text columns: ""'@"',"
>
> the problem is, sometimes,
> the cells in the TEXT column (which only have a text cell format)
> will have a 0 in them. it wont be wrapped in quotes and such:(
>
> so my choices (as far as i know) are either
> 1) find a way to apply BOTH a text AND number cell format to all cells
> 2) a format which applies to any cell, regardless of text/number value.
> (cell range A1:R323)
> 3) Conditional formatting for 0's
> (seems the easiest/most possible method)
>
> i'm not sure what other options i have atm
>
>
>
>
> "Bernie Deitrick" wrote:
>
>> Mo,
>>
>> Well, that didn't work well - the section of help that I copied was a .gif - you will need to
>> open
>> the attachment.. Sorry.
>>
>> HTH,
>> Bernie
>> MS Excel MVP
>>
>>
>> "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
>> news:O5sa81MjHHA.588@TK2MSFTNGP06.phx.gbl...
>> > Mo,
>> >
>> > You don't need to use conditional formatting: just use a custom number format. From Help:
>> >
>> >
>> >
>> > Replace the format for zeroes with "'#0"', or ""'#0"'," I'm not sure which will geive
>> > you
>> > your desired results.
>> >
>> > HTH,
>> > Bernie
>> > MS Excel MVP
>> >
>> >
>> > "Mo2" <Mo2.DeleteThis@discussions.microsoft.com> wrote in message
>> > news:EE807DC7-CF0D-497B-AC29-142D18197E0F@microsoft.com...
>> >> IF Cell value = 0, wrap with quotes
>> >> not exactly quotes.. but with this number format: ""'#0"',"
>> >> wrapped in single quotes, followed by a comma.
>> >>
>> >> (format-cells - costum , to see what it looks like)
>> >>
>> >>
>> >> also, i dont want it to apply to every column (if its possible to do that)
>> >> just A1:A323, B1:B323, E1:E323, G1:G323, I1:I323, K1:K323
>> >>
>> >> thanks in advance
>> >>
>> >>
>> >
>> >
>> >
>>
>>
>>
(Msg. 6) Posted: Wed May 02, 2007 8:58 pm
Post subject: Re: Conditional Formatting question (if cell = 0, wrap cell in quo [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
thanks very much:)
works like a charm
i finally understand what the ; are for
(well..i had an idea before, but now i get why it wasn't working in my
format testings lol)
"Bernie Deitrick" wrote:
> Mo,
>
> Custom formats have four sections: format for values greater than zero, format for values less than
> zero, formats for 0 values, and formats for string values, separated by ;'s. So:
>
> #.00;[Red]#.00;"'0"',;'@',
>
> will do something along the lines of what you want.... Not sure how you want text to appear. Just
> play with the strings in the third and fourth positions.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Mo2" <Mo2.RemoveThis@discussions.microsoft.com> wrote in message
> news:2ED38465-D12F-4C45-972A-560C06C94891@microsoft.com...
> >u mean theres attachments on this forum? where?
> > a .gif? i dont see that anywhere either... or a broken link or whatnot
> >
> >
> > Bernie, the problem is this:
> > I have a # column, a text column, # column, text column, etc
> > (and i need them in this particular order, to be copied and pasted into
> > notepad, for a reason or another)
> >
> > the cells have this format applied to them:
> > number column: ""'#0"',"
> > text columns: ""'@"',"
> >
> > the problem is, sometimes,
> > the cells in the TEXT column (which only have a text cell format)
> > will have a 0 in them. it wont be wrapped in quotes and such:(
> >
> > so my choices (as far as i know) are either
> > 1) find a way to apply BOTH a text AND number cell format to all cells
> > 2) a format which applies to any cell, regardless of text/number value.
> > (cell range A1:R323)
> > 3) Conditional formatting for 0's
> > (seems the easiest/most possible method)
> >
> > i'm not sure what other options i have atm
> >
> >
> >
> >
> > "Bernie Deitrick" wrote:
> >
> >> Mo,
> >>
> >> Well, that didn't work well - the section of help that I copied was a .gif - you will need to
> >> open
> >> the attachment.. Sorry.
> >>
> >> HTH,
> >> Bernie
> >> MS Excel MVP
> >>
> >>
> >> "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
> >> news:O5sa81MjHHA.588@TK2MSFTNGP06.phx.gbl...
> >> > Mo,
> >> >
> >> > You don't need to use conditional formatting: just use a custom number format. From Help:
> >> >
> >> >
> >> >
> >> > Replace the format for zeroes with "'#0"', or ""'#0"'," I'm not sure which will geive
> >> > you
> >> > your desired results.
> >> >
> >> > HTH,
> >> > Bernie
> >> > MS Excel MVP
> >> >
> >> >
> >> > "Mo2" <Mo2.RemoveThis@discussions.microsoft.com> wrote in message
> >> > news:EE807DC7-CF0D-497B-AC29-142D18197E0F@microsoft.com...
> >> >> IF Cell value = 0, wrap with quotes
> >> >> not exactly quotes.. but with this number format: ""'#0"',"
> >> >> wrapped in single quotes, followed by a comma.
> >> >>
> >> >> (format-cells - costum , to see what it looks like)
> >> >>
> >> >>
> >> >> also, i dont want it to apply to every column (if its possible to do that)
> >> >> just A1:A323, B1:B323, E1:E323, G1:G323, I1:I323, K1:K323
> >> >>
> >> >> thanks in advance
> >> >>
> >> >>
> >> >
> >> >
> >> >
> >>
> >>
> >>
>
>
>
(Msg. 7) Posted: Fri May 11, 2007 3:06 pm
Post subject: Re: Conditional Formatting question (if cell = 0, wrap cell in quo [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
HI, MY NAME IS MIN ZUN SEO.
I'M MICROSOFT EXCEL MVP.
YOUR ANSWER IS ALWAYS GRATEFUL.
REGARDS.
MS EXCEL MVP
MIN ZUN SEO.
"Bernie Deitrick" wrote:
> Mo,
>
> You don't need to use conditional formatting: just use a custom number format. From Help:
>
>
>
> Replace the format for zeroes with "'#0"', or ""'#0"'," I'm not sure which will geive you
> your desired results.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Mo2" <Mo2 RemoveThis @discussions.microsoft.com> wrote in message
> news:EE807DC7-CF0D-497B-AC29-142D18197E0F@microsoft.com...
> > IF Cell value = 0, wrap with quotes
> > not exactly quotes.. but with this number format: ""'#0"',"
> > wrapped in single quotes, followed by a comma.
> >
> > (format-cells - costum , to see what it looks like)
> >
> >
> > also, i dont want it to apply to every column (if its possible to do that)
> > just A1:A323, B1:B323, E1:E323, G1:G323, I1:I323, K1:K323
> >
> > thanks in advance
> >
> >
>
>
>
All times are: Eastern Time (US & Canada) (change)
Page 1 of 1
You can post new topics in this forum You can reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
Home
Forums
Home
FAQ
Profile
Private Messages
Log in
O,=ML
Office