[Solved] Conditional formatting for text
- chetan3125
- Posts: 17
- Joined: Sat Dec 17, 2011 4:39 pm
[Solved] Conditional formatting for text
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
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].
Reason: tagged [Solved].
OpenOffice 3.1 on Windows 7
Re: conditional formatting for text
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.
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
- chetan3125
- Posts: 17
- Joined: Sat Dec 17, 2011 4:39 pm
Re: conditional formatting for text
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.
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
Re: Conditional formatting for text
An other difference between FIND and SEARCH : FIND is case sensitive, SEARCH not.mriosv wrote:the difference is SEARCH() support regular expressions, which lets make complex search expressions.
Used mixed reference :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"
Select cells A1 to J1 and apply conditional formatting SEARCH("leave";$J1;1)
LibreOffice 6.4.5 on Windows 10
-
- Posts: 16
- Joined: Tue Dec 02, 2008 2:32 pm
Re: Conditional formatting for text
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!!!
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
Re: [Solved] Conditional formatting for text
"Yes", it's a formula expression which requires constant text in double-quotes.Tiger-Heli wrote: Initially, I set Condition 1 to Cell Value Equals 'Yes'
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 16
- Joined: Tue Dec 02, 2008 2:32 pm
Re: [Solved] Conditional formatting for text
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!!!
Thank you!!!
LibreOffice Portable 3.4.5 on MS Windows XP
Re: [Solved] Conditional formatting for text
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.
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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 1
- Joined: Fri May 07, 2021 6:49 pm
Re: [Solved] Conditional formatting for text
Hi,
New here but been using your suggestions without an account for years and they've all worked flawlessly until this one so I never needed to post.
Ok, I am having the exact same issue as the thread was originally posted for. I want to have conditional formatting for a column (say column a) that will highlight every cell in that column that contains a word (say heirloom) with a specific color (say yellow). I want it to work even it the cell contains other words. So if a sell contains "heirloom seeds" or "heirloom veggies" both would highlight under the same conditional formatting rule for the word "heirloom".
I have tried the above method every way I can think of and I simply cannot get it to work.
I've even tried what's mentioned at this thread: viewtopic.php?t=30992 and it's not working for me at all. With this one I replicated the exact page that was offered as an attachment by acknak, using the exact same word criteria even. Just copy and pasted the whole thing, even named my conditional formatting rule "_highlight" just like his page, and nothing seems to be working. Almost like I have something overriding it but I'm trying a new sheet each time.
I have a feeling I'm missing something very simple, like a bracket or quotation or maybe I'm defining my cell area wrong or something. Or maybe the 4.1.7 version is different for this, though that makes no sense to me either.
Anyway, I'm fed up trying to figure out what going wrong on my own so now I'm looking for help.
So if anyone can help me, maybe with a total noob brake down of this, I will pay you $1,000,000!
Ok, no I won't, I'll likely just thank you with capital letters an explanation points or an emoji or two.
New here but been using your suggestions without an account for years and they've all worked flawlessly until this one so I never needed to post.
Ok, I am having the exact same issue as the thread was originally posted for. I want to have conditional formatting for a column (say column a) that will highlight every cell in that column that contains a word (say heirloom) with a specific color (say yellow). I want it to work even it the cell contains other words. So if a sell contains "heirloom seeds" or "heirloom veggies" both would highlight under the same conditional formatting rule for the word "heirloom".
I have tried the above method every way I can think of and I simply cannot get it to work.
I've even tried what's mentioned at this thread: viewtopic.php?t=30992 and it's not working for me at all. With this one I replicated the exact page that was offered as an attachment by acknak, using the exact same word criteria even. Just copy and pasted the whole thing, even named my conditional formatting rule "_highlight" just like his page, and nothing seems to be working. Almost like I have something overriding it but I'm trying a new sheet each time.
I have a feeling I'm missing something very simple, like a bracket or quotation or maybe I'm defining my cell area wrong or something. Or maybe the 4.1.7 version is different for this, though that makes no sense to me either.
Anyway, I'm fed up trying to figure out what going wrong on my own so now I'm looking for help.
So if anyone can help me, maybe with a total noob brake down of this, I will pay you $1,000,000!
Ok, no I won't, I'll likely just thank you with capital letters an explanation points or an emoji or two.
OpenOffice 4.1.7 on Windows 10
Re: [Solved] Conditional formatting for text
The attachment from acknak in the topic you linked works fine.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: [Solved] Conditional formatting for text
The name "_highlight" is not the name of a rule. It is the name of a cell style. That's all I can tell without seeing any document from your side.michael1313b wrote:Just copy and pasted the whole thing, even named my conditional formatting rule "_highlight" just like his page, and nothing seems to be working.
http://www.openoffice.org/documentation ... c4_EN.html
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice