Compare string, wildcard, text/cell formatting

Hi.
1) I would like to compare the following:
If the string satisfies the following conditions, it returns true:
- start with some letters (eg for "no-", strings like "no", "not", "none"
satisfies so)
- have some letters in the mid (eg for "-ppl-", strings like "apple" satisfies
so)
- end with some letters

2) It would be great if wildcards (eg ?, *, + etc.) can be used in the above
functions.
If possible, how?

3) How to set it that if the string satisfies condtion stated in (1), some
""text"" AND ""cell"" formatting are made?
Eg: If condition is satisfied, the cell "I am good!" will have background yellow
and the word "good" will be bold.

Thanks.

Maybe something like:

=COUNTIF(A1,"no*ppl?*")>0

And format|conditional formatting doesn't offer that kind of control (character
by character)--it's for the whole cell.



0-0 Wai Wai ^-^ wrote:
>
> Hi.
> 1) I would like to compare the following:
> If the string satisfies the following conditions, it returns true:
> - start with some letters (eg for "no-", strings like "no", "not", "none"
> satisfies so)
> - have some letters in the mid (eg for "-ppl-", strings like "apple" satisfies
> so)
> - end with some letters
>
> 2) It would be great if wildcards (eg ?, *, + etc.) can be used in the above
> functions.
> If possible, how?
>
> 3) How to set it that if the string satisfies condtion stated in (1), some
> ""text"" AND ""cell"" formatting are made?
> Eg: If condition is satisfied, the cell "I am good!" will have background yellow
> and the word "good" will be bold.
>
> Thanks.

--

Dave Peterson

"Dave Peterson" <[email protected]> ???
news:[email protected] ???...
> Maybe something like:
>
> =COUNTIF(A1,"no*ppl?*")>0

I tried to copy the whole formula (with proper edition) in the "conditional
formatting" in vain.
Later I found out I need to set it as "formula is". :P

So is it true that:
"cell is" is only for inputting absolute values only.
"formula is" is anything aprt from absolute values [eg =F3, =sum(2,1) ]
??

> And format|conditional formatting doesn't offer that kind of control
(character
> by character)--it's for the whole cell.

How about not to use ocnditonal formatting?
Is there any formula/function which can do the formatting?

"Dave Peterson" <[email protected]> ???
news:[email protected] ???...
> Maybe something like:
>
> =COUNTIF(A1,"no*ppl?*")>0

I have a problem to apply it to a range of cells.

Normal I like to do the following
=COUNTIF(its_own_cell,"no*")>0
(where its_own_cell means, say, when the formula is applied to A1, A2 etc. it
will beocme A1, A2 etc.)

If I highlight all the cells and click on "conditonal formatting" and type the
formula, it shouldn't work.
One workaround I can think of is to apply one formula in one cell, and drag the
block square at the right botom corner, and aply the formating only. However I
have other formating already. This will clear my preset formating.
How to do?
Is there any way which apply the special formatting only?

Select all the cells in your range.

Note the activecell.

format|conditional formatting
write your formula based on that activecell.

Excel will be smart enough to adjust the formula--just like if you put it in a
cell and copied it.

(So don't use $a$1 if you want that to adjust.)



0-0 Wai Wai ^-^ wrote:
>
> "Dave Peterson" <[email protected]> ???
> news:[email protected] ???...
> > Maybe something like:
> >
> > =COUNTIF(A1,"no*ppl?*")>0
>
> I have a problem to apply it to a range of cells.
>
> Normal I like to do the following
> =COUNTIF(its_own_cell,"no*")>0
> (where its_own_cell means, say, when the formula is applied to A1, A2 etc. it
> will beocme A1, A2 etc.)
>
> If I highlight all the cells and click on "conditonal formatting" and type the
> formula, it shouldn't work.
> One workaround I can think of is to apply one formula in one cell, and drag the
> block square at the right botom corner, and aply the formating only. However I
> have other formating already. This will clear my preset formating.
> How to do?
> Is there any way which apply the special formatting only?

--

Dave Peterson

"cell is" is usually with constants, but not always.

I could use cell is
and put =B1 to check to see if that cell is equal to the value in B1
or =today() to check to see if that cell has today's date in it.

And "formula is" is pretty open ended--anything that would return true or false
(no arrays, unions, intersections) and no functions from other workbooks (UDFs).





0-0 Wai Wai ^-^ wrote:
>
> "Dave Peterson" <[email protected]> ???
> news:[email protected] ???...
> > Maybe something like:
> >
> > =COUNTIF(A1,"no*ppl?*")>0
>
> I tried to copy the whole formula (with proper edition) in the "conditional
> formatting" in vain.
> Later I found out I need to set it as "formula is". :P
>
> So is it true that:
> "cell is" is only for inputting absolute values only.
> "formula is" is anything aprt from absolute values [eg =F3, =sum(2,1) ]
> ??
>
> > And format|conditional formatting doesn't offer that kind of control
> (character
> > by character)--it's for the whole cell.
>
> How about not to use ocnditonal formatting?
> Is there any formula/function which can do the formatting?

--

Dave Peterson

> "cell is" is usually with constants, but not always.
>
> I could use cell is
> and put =B1 to check to see if that cell is equal to the value in B1
> or =today() to check to see if that cell has today's date in it.

Wow! You are so knowledgeable. :P


> And "formula is" is pretty open ended--anything that would return true or
false
> (no arrays, unions, intersections) and no functions from other workbooks
(UDFs).
>

By the way, I can even type constants too. :-)
If in doubt, better use ithis one instead, agree?
Thanks for your help.

?????????. ??????, ???????!!
My ability is very limited. Hope you will not mind to enlighten me if I do
wrongly.
"Dave Peterson" <[email protected]> ???
news:[email protected] ???...
> Select all the cells in your range.
>
> Note the activecell.
I think active cell here refers to the starting point of my first selected cell.
It is highlighted differently from all others.
Right?



> format|conditional formatting
> write your formula based on that activecell.
>
> Excel will be smart enough to adjust the formula--just like if you put it in a
> cell and copied it.
>
> (So don't use $a$1 if you want that to adjust.)

How about if I highlight the cell ranges from "name"?
What I need to do is only select the name of cell ranges (eg BaTable).
In this case, there's no active cell.
If it is solvable, how to do in this case?
Thanks! :P

>
> How about if I highlight the cell ranges from "name"?
> What I need to do is only select the name of cell ranges (eg BaTable).
> In this case, there's no active cell.
> If it is solvable, how to do in this case?
> Thanks! :P
>

Oh! Actually there's an active cell.
The reason why it disappears is I have hidden the first column of my worksheet.
Sorry for askng this stupid question.

> Select all the cells in your range.
>
> Note the activecell.
>
> format|conditional formatting
> write your formula based on that activecell.
>
> Excel will be smart enough to adjust the formula--just like if you put it in a
> cell and copied it.
>
> (So don't use $a$1 if you want that to adjust.)
>

By the way, the formula in conditional formatting is not case sensitive.
It means for the conditon ("no"), no matter which (eg no, No, nO, NO), can
satisfy so.
Do you know if there's a way to distinguish them?

> If in doubt, better use ithis one instead, agree?

I've used "formula is" a few times when "cell value is" would have worked fine.

For me, it's just what I think of first (well, if it works!).



0-0 Wai Wai ^-^ wrote:
>
> > "cell is" is usually with constants, but not always.
> >
> > I could use cell is
> > and put =B1 to check to see if that cell is equal to the value in B1
> > or =today() to check to see if that cell has today's date in it.
>
> Wow! You are so knowledgeable. :P
>
> > And "formula is" is pretty open ended--anything that would return true or
> false
> > (no arrays, unions, intersections) and no functions from other workbooks
> (UDFs).
> >
>
> By the way, I can even type constants too. :-)
> If in doubt, better use ithis one instead, agree?
> Thanks for your help.

--

Dave Peterson

The formula in the "formula is" formula (too many formulas!) works the same way
as a formula works in a cell.

There's another function that you may want to use: =Exact().

=Exact("no","No")
will be false

=exact(a1,b1)
if you want to refer to cells.

There's more information in xl's help for =exact().


0-0 Wai Wai ^-^ wrote:
>
> > Select all the cells in your range.
> >
> > Note the activecell.
> >
> > format|conditional formatting
> > write your formula based on that activecell.
> >
> > Excel will be smart enough to adjust the formula--just like if you put it in a
> > cell and copied it.
> >
> > (So don't use $a$1 if you want that to adjust.)
> >
>
> By the way, the formula in conditional formatting is not case sensitive.
> It means for the conditon ("no"), no matter which (eg no, No, nO, NO), can
> satisfy so.
> Do you know if there's a way to distinguish them?

--

Dave Peterson

"Dave Peterson" <[email protected]> ???
news:[email protected] ???...
> The formula in the "formula is" formula (too many formulas!) works the same
way
> as a formula works in a cell.
>
> There's another function that you may want to use: =Exact().
>
> =Exact("no","No")
> will be false
>
> =exact(a1,b1)
> if you want to refer to cells.
>
> There's more information in xl's help for =exact().

But Exact doesn't support wildcards as I expect.
So it is not really what I want.

Anyway, thanks for your info.

>
> 0-0 Wai Wai ^-^ wrote:
> >
> > > Select all the cells in your range.
> > >
> > > Note the activecell.
> > >
> > > format|conditional formatting
> > > write your formula based on that activecell.
> > >
> > > Excel will be smart enough to adjust the formula--just like if you put it
in a
> > > cell and copied it.
> > >
> > > (So don't use $a$1 if you want that to adjust.)
> > >
> >
> > By the way, the formula in conditional formatting is not case sensitive.
> > It means for the conditon ("no"), no matter which (eg no, No, nO, NO), can
> > satisfy so.
> > Do you know if there's a way to distinguish them?
>
> --
>
> Dave Peterson