Using regex as part of logical condition for cell

Discuss the spreadsheet application

Using regex as part of logical condition for cell

Postby PopcornDave » Tue Dec 04, 2007 4:51 am

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!
PopcornDave
 
Posts: 2
Joined: Tue Dec 04, 2007 2:28 am

Re: Using regex as part of logical condition for cell

Postby huw » Tue Dec 04, 2007 11:43 am

Regex don't work in IFs.

There are the beginnings of a Calc regex howto, which also link to the highly informative Writer version.
huw
Volunteer
 
Posts: 417
Joined: Wed Nov 21, 2007 1:57 pm

Re: Using regex as part of logical condition for cell

Postby PopcornDave » Tue Dec 04, 2007 9:34 pm

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?
PopcornDave
 
Posts: 2
Joined: Tue Dec 04, 2007 2:28 am

Re: Using regex as part of logical condition for cell

Postby Villeroy » Tue Dec 04, 2007 11:48 pm

IF(ISNUMBER(SEARCH("^Bla";A1);...
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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27379
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Using regex as part of logical condition for cell

Postby JohnV » Wed Dec 05, 2007 5:53 pm

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.
JohnV
Volunteer
 
Posts: 1585
Joined: Mon Oct 08, 2007 1:32 am
Location: Kentucky, USA

Re: Using regex as part of logical condition for cell

Postby huw » Wed Dec 05, 2007 6:01 pm

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.

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.
huw
Volunteer
 
Posts: 417
Joined: Wed Nov 21, 2007 1:57 pm

Re: Using regex as part of logical condition for cell

Postby JohnV » Wed Dec 05, 2007 7:04 pm

huw,
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.
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.
JohnV
Volunteer
 
Posts: 1585
Joined: Mon Oct 08, 2007 1:32 am
Location: Kentucky, USA


Return to Calc

Who is online

Users browsing this forum: No registered users and 44 guests