Compare string, wildcard, text/cell formatting

  • Thread starter Thread starter 0-0 Wai Wai ^-^
  • Start date Start date
0

0-0 Wai Wai ^-^

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.
 
Dave Peterson said:
Maybe something like:

=COUNTIF(A1,"no*ppl?*")>0
Click to expand...

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.
Click to expand...

How about not to use ocnditonal formatting?
Is there any formula/function which can do the formatting?
 
Dave Peterson said:
Maybe something like:

=COUNTIF(A1,"no*ppl?*")>0
Click to expand...

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.)
 
"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 ^-^ said:
Dave Peterson said:
Maybe something like:

=COUNTIF(A1,"no*ppl?*")>0
Click to expand...

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.
Click to expand...

How about not to use ocnditonal formatting?
Is there any formula/function which can do the formatting?
Click to expand...
 
"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.
Click to expand...

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).
Click to expand...

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 said:
Select all the cells in your range.

Note the activecell.
Click to expand...
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.)
Click to expand...

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
Click to expand...

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.)
Click to expand...

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!).
 
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().
 
Dave Peterson said:
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().
Click to expand...

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

Anyway, thanks for your info.
 
Back
Top