[Solved] Enable re expressions for range instead of globally

[jjon]

Again, this is probably obvious, but it's escaping me.

I've got a COUNTIF formula for one column of cells that depends on regex being enabled. I have a VLOOKUP formula in the cells of another column that fails if regex in formulas is enabled.

Is there any way to selectively enable regular expressions in formulas within a defined range instead of globally? Or, the other way around: disable regex in formulas for a range of cells?

(If A2 contains the string "B+", my VLOOKUP formula =VLOOKUP(A2;Scales.$C$14:$D$24;2;0) reads A2 as if it were a regular expression.)

[MrProgrammer]

Is there any way to selectively enable regular expressions in formulas within a defined range instead of globally?

OpenOffice does not provide that capability. The Enable regular expressions in formulas setting is global for the entire spreadsheet.

Or, the other way around: disable regex in formulas for a range of cells?

If the only problem character is +, leave regular expressions enabled and use =VLOOKUP(SUBSTITUTE(cell;"+";"\+");range;column;type). Otherwise review the post from jvz1 in [Tutorial] VLOOKUP questions and answers.

Or, you might be able to alter the COUNTIF formula so it can produce the desired result with regular expressions disabled. For further assistance attach a document demonstrating the situation (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the document itself).

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[jjon]

Yes! Many thanks! I couldn't figure out how to escape the "+". This does it neatly: SUBSTITUTE(cell;"+";"\+"). Thanks for the pointers to the tutorial as well.

[Lupp]

VLOOKUP is rather inflexible. Using a combination of INDEX and MATCH you can control things to more detail:

Code: Select all

{=INDEX(columnArrayToSelectFrom; MATCH(TRUE(); ISNUMBER(SEARCH(RegEx; columnArrayToSearch)); 0))}
or
{=INDEX(columnArrayToSelectFrom; MATCH(TRUE(); ISNUMBER(Find(Literal; columnArrayToSearch)); 0))}
or (e.g.)
{ =INDEX(columnArrayToSelectFrom; MATCH(TRUE(); columnArrayToSearch <= expression; 0))}

Of course, you may have to pay with reduced efficiency in cases where VLOOKUP can achieve what you need.
In cases of matrices with many columns (of which only the first one and an additional one are use) VLOOKUP can also be inefficient, at least in storage.

[jjon]

VLOOKUP is rather inflexible. Using a combination of INDEX and MATCH you can control things to more detail:

Thanks a lot for this. I had to tinker with it for some time to see the way this works, but you're right, it is more flexible. Attached a demonstration file in case it's of help to anyone