Conditional formatting problem

The objective is to enter in a text and using the Len with SUSTITUTE in my conditional formatting rule locate those strings that have the value I entered in B14. The rule I'm
using is shown in C4. I can't find my problem - nothing is located
Frank FreeseAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

I dont think the commands you are using have anything to do with what you are trying to do.

LEN counts the length of a string. I dont see how that is going to help you. But I might just not be seeing it.

That being said, you can do this with the filtering built into excel. Just Choose the Column with your data in it and go to Data --> Filter.

Then click the arrow at the top of that column and go to Text Filter --> Contains. Then type in "new". That will return # 2, 3, 7, 10, and B14.
Frank FreeseAuthor Commented:
You may be right, however, the example I am using the SUBSTITUTE function to search for text. The search text is specified in cell B13 and the conditional formatting rule is:
Harry LeeCommented:

I'm not sure if I understand your question correctly. Why bother with Len? Why don't you simply configure your conditional formation using if Cell Value contains (Specific Text) B14?

Just look at my sample.
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Frank FreeseAuthor Commented:
I'm taking my example from a book and it may be possible that the book is wrong. I can accept that since I'm learning Conditional Formating. The objective of the exercise is to enter in some text and through conditional formating locate every occrrence of that text and format each finding. Can you get me there please
Harry LeeCommented:

As I said in previous post, if all you want is to highlight cells that contains a certain word or phrase, it's the best to use if Cell (Specific Text) contains rather than using formula.

Why re-invent the wheel if Excel has the feature already built in nicely? Sometimes, these Excel books are not written very nicely. The author just ran out of things to say, and starting making up example that doesn't make any sense.
Frank FreeseAuthor Commented:
I'm sure running into too many problems with what I am working with. I do not disagree, at all with your comments, and as I investigated the LEN and SUBSTITUTE functions the math just did not apply.
If I correctly understand what you are suggesting my conditional rule would look like this?
Frank FreeseAuthor Commented:
I need help on my conditional statement please
barry houdiniCommented:
Hello fh_freese,

Using the original approach you'd need this formula


because you need to set up the rule as it applies to the first cell in the range, hence you refer to B3 only not the whole range.....and B$14 needs the $ to ensure that it doesn't change for each row

Because SUBSTITUTE is case-sensitive that will only format B9 because other rows only contain New with a capital a version that is not case-sensitive but still using that approach would be


......but you can get the same result more simply with this formula


That will highlight B4, B5, B9 and B12 - if you don't want B12 highlighted because you only want exact word matches then go with this version

=SEARCH(" "&B$14&" "," "&B3&" ")

regards, barry

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Frank FreeseAuthor Commented:
OK Barry, you nailed it with the first SEARCH - the LEN and SUBSTITUE only worked on the first cell - but I'm happy to find a easier way.
I was looking at the SEARCH when you posted.
Thanking you so very much
Frank FreeseAuthor Commented:
simply fantastic - much appreciated
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.