Conditional formatting depending on sub string content i cell

[Andreas Lundgren]

Hi!

This has been eluded me all afternoon, I just want to mark out cells
in Excel that contains a specific sub string.

I have thousands of rows all containing small text strings. I would
like to mark the once containing one specific word with blue text, and
other cells containing another specific key word with a different
color.

Very simple, don't care about corner cases (containing several key
words etc.) Mostly I'm annoyed that I cannot solve this simple
problem. I just cannot get Excel to understand any of the formulas I
try in conditional formatting.

Can anyone hint me how to solve this? I would be most grateful!

(I run Excel 2003 by the way)

BR,
Andreas - Sweden

 

[Pete_UK]

Highlight the cells you want this to apply to (assume that the first
cell in the highlighted range is A2), and click on Format |
Conditional formatting to bring up the CF dialogue box. In the first
box you should choose Formula Is rather than Cell Value Is and then
put this formula in the next box:

=ISNUMBER(SEARCH("word",A2))

Click on the Format button, then on Colour, and choose your colour. OK
your way out.

Obviously, you should substitute your word or phrase in the formula
above - it will adjust automatically to the other cells in the range.

If you then want another condition you can bring up the CF dialogue
box again, click on Add and repeat for a different word or phrase and
with a different colour.

Hope this helps.

Pete

 

[Andreas Lundgren]

Hi all, and thanks Pete!

It did help, ALMOST straight out-of-the-box...

I was playing around with FIND and other Excel commands yesterday, but
I constantly got "The formula you typed in contains an error" even
though I was just cut'n'paste from all kinds of tutorials. I got the
same error when trying your string!

BUT!
When I replaced the "," with a ";" it works!!!

In Nordic countries, we use "," as a decimal delimiter (instead of
".") I guess this must be the problem! I run an English language
Win2000, English Office but that did not matter. I did try to change
my regional settings in the Control Panel to use "." and not "," as
decimal delimiter, but it did not changed the fact that I needed ";"
and not "," in Excel. (I did not bather to reboot, maybe that is
needed to update the system.)

Even now, I cannot find anything about this in Excel help. For
example, SUM in the help section states the syntax as "SUM
(number1,number2, ...)".

(And for you all native English people I can tell you an other
interesting fact. Microsoft did actually translate all the Excel
commands to different languages. "SUM" for example is "SUMMA" in a
Swedish version of Excel. This makes it impossible to open documents
correctly that are created in an English version of Excel since the
keyword "SUM" is not recognized!)

Thanks a lot for the help,
Andreas - Sweden.

 

[AdamV]

I've done some work in the past with a client with German and UK offices
and come across similar problems with needing to use ; to separate
arguments - it's really hard to retrain your muscle memory to do this
fluidly though!
As for translating formulas I've never had a problem with that, saving
in one version and re-opening in another. Maybe we had the MUI installed
on some machines and that avoided the issue. Not sure why, but it
certainly never caused a problem.
I could see that you might have problems if you had VBA which wrote
formulas into cells for you, since there would be no conversion there.

 

[Pete_UK]

You're welcome, Andreas, and thanks for the feedback.

Pete