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.)
[Solved] Enable re expressions for range instead of globally
[Solved] Enable re expressions for range instead of globally
Last edited by jjon on Sun May 12, 2019 3:49 am, edited 1 time in total.
OpenOffice 4.1.6
macOS 10.12.6 (16G29)
macOS 10.12.6 (16G29)
- MrProgrammer
- Moderator
- Posts: 4907
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: enable regular expressions for range instead of globally
OpenOffice does not provide that capability. The Enable regular expressions in formulas setting is global for the entire spreadsheet.jjon wrote:Is there any way to selectively enable regular expressions in formulas within a defined range instead of globally?
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.jjon wrote:Or, the other way around: disable regex in formulas for a range of cells?
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.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
[Solved]enable regular expressions for range instead of glob
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.
OpenOffice 4.1.6
macOS 10.12.6 (16G29)
macOS 10.12.6 (16G29)
Re: enable regular expressions for range instead of globally
VLOOKUP is rather inflexible. Using a combination of INDEX and MATCH you can control things to more detail:
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.
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))}
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.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München
Re: enable regular expressions for range instead of globally
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 anyoneLupp wrote:VLOOKUP is rather inflexible. Using a combination of INDEX and MATCH you can control things to more detail:
- Attachments
-
- testingLOfunctions.ods
- (27.48 KiB) Downloaded 93 times
OpenOffice 4.1.6
macOS 10.12.6 (16G29)
macOS 10.12.6 (16G29)