| Summary: | Make the option "Enable regular expression in formulas" less confusing. | ||
|---|---|---|---|
| Product: | LibreOffice | Reporter: | Greg Wright <greg> |
| Component: | Calc | Assignee: | Not Assigned <libreoffice-bugs> |
| Status: | RESOLVED NOTABUG | ||
| Severity: | enhancement | CC: | danieldonadel, gerard.fargeot, jerome.bouat |
| Priority: | medium | ||
| Version: | 3.6.1.2 release | ||
| Hardware: | Other | ||
| OS: | All | ||
| Whiteboard: | BSA | ||
| Crash report or crash signature: | Regression By: | ||
| Attachments: | A spreadhseet that represents reproducing the error. | ||
Confirmed in 3.6.1.2 (Windows) parentheses are REGular EXpressions, just deselect "Enable regular expressions in formula" in Tools > Options > LO Calc > Calculate. or use "escape character" \ (see in Help) Having regexp ON by default and its usage in the vlookup function pose both compatibility problems and issues with less experienced users. Either vlooukup should use a further optional parameter to specify that a query is using regexp rather than normal text or a different function for regexp queries should be implemented. Version should be the oldest version confirmed. Confirmed in 4.1.6.2 (Windows). What about a document which is shared between 2 users which don't have the same option value for "Enable regular expressions in formula" ? (In reply to comment #5) > Confirmed in 4.1.6.2 (Windows). > > What about a document which is shared between 2 users which don't have the > same option value for "Enable regular expressions in formula" ? This option is not stored in the user profile, but within the document. So, both users will have the same result. (Except if saving in a non-.ods format like .xls. In this last case, Regex are always disabled.) *** Bug 82261 has been marked as a duplicate of this bug. *** (In reply to comment #3) > Having regexp ON by default and its usage in the vlookup function pose both > compatibility problems and issues with less experienced users. > Either vlooukup should use a further optional parameter to specify that a > query is using regexp rather than normal text or a different function for > regexp queries should be implemented. Antonello, I agree if you, REGEXP should be OFF by default. I had some real trouble when I switched from MsExcel and was not aware about it. I'm new here. How could I propose that? Propose a better evaluating on turning the default to off? Can we set this status for RESOLVED? That's true that the former M. Excel users are confused. However, I think the main reason why the REGEXP should be OFF by default is that vlookup is mostly used for searching an exact match of a fixed value. Keep in mind that the search value is mostly a reference to a cell value. Regexp is a powerfull feature but I don't think it is often used with vlookup. |
Created attachment 62620 [details] A spreadhseet that represents reproducing the error. Problem description: When using VLOOKUP to lookup a text value and return a secondary column with false SORT, doesn't return the second column. Steps to reproduce: 1. Create 1st (Sheet1) with list of values names and ages using 3 rows, two columns. One value must contain parentheses. 2. Create 2nd (Sheet2) with two names from the first sheet. One must be the value with the parentheses. 3. Add formula in second column to retrive the second column, the age, =VLOOKUP(A3,Sheet1.A3:B5,2,0) This will return the #N/A error. Take out the parentheses from the first and second sheets and the column is returned. Current behavior: Returns #N/A Expected behavior: Value from second column. Platform (if different from the browser): Browser: Mozilla/5.0 (Windows NT 6.0; WOW64) AppleWebKit/536.5 (KHTML, like Gecko) Chrome/19.0.1084.52 Safari/536.5