[Solved] Conditional formatting for text

Discuss the spreadsheet application

[Solved] Conditional formatting for text

Postby chetan3125 » Sun Jan 01, 2012 6:59 pm

Hi guys,
I am stuck on a small problem.
I have to fill the cell color red if the cell contains specific text through conditional formatting in calc.
eg. if cell contains text "leave" the cell should have red fill
(also if the cell contains "half day leave" or "full day leave" the cell should have red fill (basically even if the cell contains the word leave with many other words it should have red fill)
Is it possible in calc?
This is same like the function of "text contains" in excel conditional formatting.
Any help would be appreciated.
Thanks
Last edited by Hagar Delest on Sun Jan 01, 2012 10:52 pm, edited 1 time in total.
Reason: tagged [Solved].
OpenOffice 3.1 on Windows 7
User avatar
chetan3125
 
Posts: 17
Joined: Sat Dec 17, 2011 4:39 pm

Re: conditional formatting for text

Postby mriosv » Sun Jan 01, 2012 7:16 pm

In conditional formatting, change "Field value is" to "Formula is", and use a formula like:
FIND("leave";A1;1) or
SEARCH("leave";A1;1)
the difference is SEARCH() support regular expressions, which lets make complex search expressions.
LibreOffice 3.5.4 AOo-3.4 on Win 7 Ultimate
mriosv
Volunteer
 
Posts: 651
Joined: Mon Mar 09, 2009 1:12 am
Location: Galiza (España)

Re: conditional formatting for text

Postby chetan3125 » Sun Jan 01, 2012 7:48 pm

mriosv wrote:In conditional formatting, change "Field value is" to "Formula is", and use a formula like:
FIND("leave";A1;1) or
SEARCH("leave";A1;1)
the difference is SEARCH() support regular expressions, which lets make complex search expressions.



Thanks a lot mriosv
This solved my problem.
:bravo: :bravo: :bravo:
Just one small question-
If I apply this conditional formatting (yellow fill) in column J and suppose if cell J1 contains word "leave",
Is it possible to apply same formatting (yellow fill) in cell A1 to J1 (a part of row) if J1 contains word "leave"
Thanks !
OpenOffice 3.1 on Windows 7
User avatar
chetan3125
 
Posts: 17
Joined: Sat Dec 17, 2011 4:39 pm

Re: Conditional formatting for text

Postby gerard24 » Sun Jan 01, 2012 8:27 pm

mriosv wrote:the difference is SEARCH() support regular expressions, which lets make complex search expressions.


An other difference between FIND and SEARCH : FIND is case sensitive, SEARCH not.

chetan3125 wrote:Is it possible to apply same formatting (yellow fill) in cell A1 to J1 (a part of row) if J1 contains word "leave"

Used mixed reference :
Select cells A1 to J1 and apply conditional formatting SEARCH("leave";$J1;1)
LibreOffice 6.2.8 on Windows 10
gerard24
Volunteer
 
Posts: 952
Joined: Sat Oct 30, 2010 5:12 pm
Location: France

Re: Conditional formatting for text

Postby Tiger-Heli » Tue Jul 10, 2012 6:49 pm

LibreOffice 3.5.4.2 - WinXP

I'm trying to do something similar and not having much luck.

Basically, I want a cell to have green text if it has the value "Yes" and red text if it has the value "No". (And "Yes-other text" should not be changed).

I set up styles named Red and Green.

Then I selected all cells and went to Conditional Formatting under Format.

Initially, I set Condition 1 to Cell Value Equals 'Yes' and set Style to Green, and Condition 2 Cell Value Equals 'No" and Set Style to Red.
Based on this thread, I tried setting Condition 1 to Formula and "FIND("Yes",A1,1)", and then "SEARCH("Yes",A1,1)", but neither one worked for me.

Thanks!!!
LibreOffice Portable 3.4.5 on MS Windows XP
Tiger-Heli
 
Posts: 16
Joined: Tue Dec 02, 2008 2:32 pm

Re: [Solved] Conditional formatting for text

Postby Villeroy » Tue Jul 10, 2012 7:33 pm

Tiger-Heli wrote:Initially, I set Condition 1 to Cell Value Equals 'Yes'

"Yes", it's a formula expression which requires constant text in double-quotes.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27376
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Conditional formatting for text

Postby Tiger-Heli » Tue Jul 10, 2012 7:48 pm

Awesome - Cell Value equals "Yes" worked fine. Formula equals Search or Find did not work, but "Yes" did what I was looking for.

Thank you!!!
LibreOffice Portable 3.4.5 on MS Windows XP
Tiger-Heli
 
Posts: 16
Joined: Tue Dec 02, 2008 2:32 pm

Re: [Solved] Conditional formatting for text

Postby Villeroy » Tue Jul 10, 2012 8:00 pm

Test any formula expressions in cells before you use them in conditional formats.
FIND("Yes";A1;1) returns error #VALUE! if "Yes" is not in A1.
ISNUMBER(FIND("Yes";A1;1)) should work.
ISNUMBER(SEARCH("Yes";A1;1)) is case-insensitive.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27376
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Calc

Who is online

Users browsing this forum: Zizi64 and 28 guests