J
JPS
How do I change the color of a cell to green if the cell contains the phrase
"TBN" anywhere within the text
"TBN" anywhere within the text
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
G
Gary''s Student
Say for cell A1, use Formula Is:
=LEN(SUBSTITUTE(A1,"TBN",""))<>LEN(A1)
=LEN(SUBSTITUTE(A1,"TBN",""))<>LEN(A1)
J
JPS
If TBN is found anywhere within the text of a cell, I need the color of the
font for all of the text in that cell to be green.
font for all of the text in that cell to be green.
D
David Biddulph
Gary's formula in CF will do that.
If you want also to deal with "tbn" in lower case, change his CF formula to
=LEN(SUBSTITUTE(UPPER(A1),"TBN",""))<>LEN(A1)
If you want also to deal with "tbn" in lower case, change his CF formula to
=LEN(SUBSTITUTE(UPPER(A1),"TBN",""))<>LEN(A1)
J
JPS
David,
I actually need the formula to update column D, does the formula need to
cahnge any?
I actually need the formula to update column D, does the formula need to
cahnge any?
D
David Biddulph
Put the formula in the Conditional Formatting conditions for whichever
column you want the formatting to occur. In the formula, refer to the cell
whose contents you want to affect the formating.
You can use absolute addressing (the $ prefix) if you wish to apply a format
across a range of columns without it changing the reference. So, for
example, if you wanted columns D to G to be affected by the content of
column B, you could use the CF formula
=LEN(SUBSTITUTE(UPPER($B1),"TBN",""))<>LEN($B1) in D1 and it would copy
unchanged across the other columns.
column you want the formatting to occur. In the formula, refer to the cell
whose contents you want to affect the formating.
You can use absolute addressing (the $ prefix) if you wish to apply a format
across a range of columns without it changing the reference. So, for
example, if you wanted columns D to G to be affected by the content of
column B, you could use the CF formula
=LEN(SUBSTITUTE(UPPER($B1),"TBN",""))<>LEN($B1) in D1 and it would copy
unchanged across the other columns.
J
JPS
David,
Perfecto... Thanks for the patience. Now that I know the formula works,
please walk me through the formula so I can learn the how & why.
Perfecto... Thanks for the patience. Now that I know the formula works,
please walk me through the formula so I can learn the how & why.
D
David Biddulph
The functions in the formula are each standard Excel functions:
LEN
SUBSTITUTE
UPPER
For any Excel function (except DATEDIF), you will find an explanation of the
fiunction and its syntax, and usually examples and a "See Also" link to
related functions, if you type the name of the function into Excel help.
LEN
SUBSTITUTE
UPPER
For any Excel function (except DATEDIF), you will find an explanation of the
fiunction and its syntax, and usually examples and a "See Also" link to
related functions, if you type the name of the function into Excel help.