Using regex as part of logical condition for cell

[PopcornDave]

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!

[huw]

Regex don't work in IFs.

There are the beginnings of a Calc regex howto, which also link to the highly informative Writer version.

[PopcornDave]

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?

[Villeroy]

IF(ISNUMBER(SEARCH("^Bla";A1);...
IF(LEFT(A1;3)="Bla";...
IF(MID(A1;5;3)="Bla";...

[JohnV]

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.

[huw]

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.

[JohnV]

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.