I ran in to a problem trying to accomplish this today while I was trying to base a condition on part of a cell's contents.
What I had was one cell with "Sold 10/13/06" and I wanted to base the condition of the adjoining cell based on just the word "Sold".
I tried to do =IF((<blah>)OR(^Sold*);"";a3+b3) so that the adjacent column added the two columns based on either <blah> or if the cell contained "Sold" in the beginning of the text in the cell, but it didn't work.
I thought I had read in the help file that Calc could use regular expressions but apparently I'm not doing it correctly.
Can someone point me in the right direction on this?
Thanks!
Using regex as part of logical condition for cell
-
- Posts: 2
- Joined: Tue Dec 04, 2007 2:28 am
Re: Using regex as part of logical condition for cell
Regex don't work in IFs.
There are the beginnings of a Calc regex howto, which also link to the highly informative Writer version.
There are the beginnings of a Calc regex howto, which also link to the highly informative Writer version.
-
- Posts: 2
- Joined: Tue Dec 04, 2007 2:28 am
Re: Using regex as part of logical condition for cell
Thanks for that!
I got around it by putting the text "Sold" in one column so that I could check it and put the date that was with the text in a separate column. It worked, but it seemed like a clunky solution.
If I wanted to find just part of the text in a cell, and I knew the position, would I have to be looking at substrings of the data in the cell or does that not work in an =IF situation either?
I got around it by putting the text "Sold" in one column so that I could check it and put the date that was with the text in a separate column. It worked, but it seemed like a clunky solution.
If I wanted to find just part of the text in a cell, and I knew the position, would I have to be looking at substrings of the data in the cell or does that not work in an =IF situation either?
Re: Using regex as part of logical condition for cell
IF(ISNUMBER(SEARCH("^Bla";A1);...
IF(LEFT(A1;3)="Bla";...
IF(MID(A1;5;3)="Bla";...
IF(LEFT(A1;3)="Bla";...
IF(MID(A1;5;3)="Bla";...
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: Using regex as part of logical condition for cell
With "Sold" in A1 this will work:
=IF(A1="Sold";"Sold";"Not Sold")
With "Sold 10/12/07" in A1 this should work but doesn't in 2.3:
=IF(A1="Sold.*";"Sold";"Not Sold")
Note that at Tools > Options > Calc > Calculate, regular expressions must be enabled.
=IF(A1="Sold";"Sold";"Not Sold")
With "Sold 10/12/07" in A1 this should work but doesn't in 2.3:
=IF(A1="Sold.*";"Sold";"Not Sold")
Note that at Tools > Options > Calc > Calculate, regular expressions must be enabled.
Re: Using regex as part of logical condition for cell
It's not easy to find in Calc Help, but only SUMIF, COUNTIF, MATCH, SEARCH, LOOKUP, HLOOKUP, VLOOKUP, DCOUNT, DCOUNTA, DSUM, DPRODUCT, DMAX, DMIN, DAVERAGE, DSTDEV, DSTDEVP, DVAR, DVARP, DGET are supposed to work with regex. See the HowTo linked above.JohnV wrote:this should work but doesn't in 2.3:
=IF(A1="Sold.*";"Sold";"Not Sold")
Note that at Tools > Options > Calc > Calculate, regular expressions must be enabled.
Re: Using regex as part of logical condition for cell
huw,
You are correct. I thought I had used regex with IF before but I just tried it in 1.1.5 and it doesn't work there either.It's not easy to find in Calc Help, but only SUMIF, COUNTIF, MATCH, SEARCH, LOOKUP, HLOOKUP, VLOOKUP, DCOUNT, DCOUNTA, DSUM, DPRODUCT, DMAX, DMIN, DAVERAGE, DSTDEV, DSTDEVP, DVAR, DVARP, DGET are supposed to work with regex. See the HowTo linked above.