[Solved] Enable re expressions for range instead of globally

Discuss the spreadsheet application

[Solved] Enable re expressions for range instead of globally

Postby jjon » Sat May 11, 2019 11:26 pm

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.)
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)
jjon
 
Posts: 7
Joined: Fri Apr 19, 2019 11:40 pm

Re: enable regular expressions for range instead of globally

Postby MrProgrammer » Sat May 11, 2019 11:45 pm

jjon wrote: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.

jjon wrote: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.
Mr. Programmer
AOO 4.1.5 Build 9789 on MacOS 10.11.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3715
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

[Solved]enable regular expressions for range instead of glob

Postby jjon » Sun May 12, 2019 12:04 am

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)
jjon
 
Posts: 7
Joined: Fri Apr 19, 2019 11:40 pm

Re: enable regular expressions for range instead of globally

Postby Lupp » Sun May 12, 2019 12:21 am

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

Code: Select all   Expand viewCollapse view
{=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.
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2489
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: enable regular expressions for range instead of globally

Postby jjon » Mon May 27, 2019 4:40 am

Lupp wrote: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
Attachments
testingLOfunctions.ods
(27.48 KiB) Downloaded 5 times
OpenOffice 4.1.6
macOS 10.12.6 (16G29)
jjon
 
Posts: 7
Joined: Fri Apr 19, 2019 11:40 pm


Return to Calc

Who is online

Users browsing this forum: No registered users and 30 guests