Changes to Calc Find & Replace dialog

Request For Enhancement, User feedback candidates for wiki
Post Reply
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Changes to Calc Find & Replace dialog

Post by FJCC »

Damjan Jovanovic is asking on the dev mailing list for suggests about changing the Find & Replace dialog in Calc. He has fixed a couple of bugs already and wants to collect suggestions while the code is fresh in his mind. His message was sent about four hours ago and he proposes several possibilities.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Changes to Calc Find & Replace dialog

Post by Villeroy »

Some options are preserved during the session, others permanently, some options are not preserved at all.
Regex is saved permanently. Why?
Current selection is not saved at all. LO checks "current selection" when more than a single cell is selected. This makes a lot of sense because when I select one or more ranges before calling the Find&Replace command then I want to search in the current selection.
And I don't like the [More Options] option in this dialog and many others. Showing all the options is not too much overload, particularly when you can not be sure which option is preserved.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
MrProgrammer
Moderator
Posts: 4895
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Changes to Calc Find & Replace dialog

Post by MrProgrammer »

Villeroy wrote:LO checks "current selection" when more than a single cell is selected. This makes a lot of sense because when I select one or more ranges before calling the Find&Replace command then I want to search in the current selection.
+1
Initialize Current Selection to on if more than one cell is selected, else off, similar to the way that Writer works with text selection.
Villeroy wrote:And I don't like the [More Options] option in this dialog …
+1
Eliminate More Options in this dialog. I always have to expand the dialog since it is easy to forget which options might have been preserved from a previous Find & Replace. My keyboard shortcut is: ⌘F (Find & Replace) ⌘O (More Options).
FJCC wrote:Damjan Jovanovic is asking on the dev mailing list for suggests about changing the Find & Replace dialog in Calc.
Search In only has three choices: Formulas, Values, Notes. Use radio buttons for the choices instead of a dropdown.
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).
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Changes to Calc Find & Replace dialog

Post by RoryOF »

I agree that the "More Option" options should be brought forward to the main F&R screen, but in interest of uniformity, that is a change that ought be applied across all OO applications.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Changes to Calc Find & Replace dialog

Post by Villeroy »

MrProgrammer wrote:Search In only has three choices: Formulas, Values, Notes. Use radio buttons for the choices instead of a dropdown.
And there is the extra option to search Styles which also excludes Formulas, Values, Notes. I'm not 100% sure but I think there could be 4 search modes Formulas, Values, Notes and Styles. I'm convinced that these should remain exclusive to each other.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
dacha
Posts: 10
Joined: Fri Jul 15, 2011 10:18 am

Re: Changes to Calc Find & Replace dialog

Post by dacha »

Hi

I am the developer who was asking. Thank you for your comments so far. "Current selection only" is now checked when the "Find & Replace" dialog is opened with cells already selected. Here were my other questions:

Visibility of options. In a comment on #87032 it is suggested the dialog should always be expanded so all options are visible, but #39453 is all about trying to shrink it as it hides cells under it. #39453 was filed in 2004 when monitors were smaller and had lower resolutions, so dialog size mattered more, but small devices are becoming ubiquitous today too. If the dialog should remain expandable, which options should be visible with it contracted? Comments in #35093 propose including "Current selection only" and "Regular expressions", although I think the "Search in" is far more commonly used than "Regular expressions".

Search in what? #106857 wants the default to be "Values" instead of the current "Formulas", but Excel (2007) uses "Formulas", so it might surprise users even though it is more natural.

Columns or rows? #106857 again wants "Columns" as default even though Excel (2007) has rows.

Use the value of the cell under the cursor as the find text? #73544 asks for this. Calc currently uses the last value searched for instead, as does Excel (2007). Instead of the cell under the cursor, we could use a single selected cell instead: it is rather hard to select only 1 cell by accident (with the cursor on it, you have to Ctrl+Click it or Shift+Right+Left), and you wouldn't need to search just 1 cell, so I think it's safe to treat a single selected cell as the find text. Another AOO innovation?

Selection during the dialog? If cells are selected during the dialog, should we automatically check the "Current selection only" box? We currently uncheck it and gray it out if cells are deselected during the dialog, but only enable it without checking it if cells are selected again. Excel (2007) doesn't even have this option: if cells are selected only they will be searched/replaced, if no cells are selected all will be. If we want to copy Excel's behaviour then we should check it on reselection. But Writer doesn't do that for multi-line text selections either, so it should probably be changed too (and other apps?).
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Changes to Calc Find & Replace dialog

Post by FJCC »

In the absence of any broad based information about how the dialog is actually used, I would hesitate to change any of the default behavior. The one change of that type that really makes sense to me is setting Current Selection Only when multiple cells are selected. However, I think the setting would have to be visible in the dialog by default. I would be fine with having the More Options section open by default. If the amount of screen space taken up by that is a big concern, what about moving Current Selection Only and Search In into the part of the dialog that is visible by default? I suspect Regular Expressions are not used by most users.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
User avatar
MrProgrammer
Moderator
Posts: 4895
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Changes to Calc Find & Replace dialog

Post by MrProgrammer »

I'd like to mention a small bug which occurs in OOo 3.2 though it may have been fixed in later releases. Create a sheet with X in call A1 and all other cells empty. Make A1 the active cell, for example by clicking. A search for X now fails: Search key not found! Find All will select the cell, but then Find will again report Search key not found. If the bug still exists, this would be a good time to investigate it.

I hardly ever use Search In = Values because it has the side effect, during Replace, of converting the cell's formula to it's value. I have on very rare occasions used it for a Find All operation to select relevant cells. I would prefer Formulas to remain as the default.

I like the idea of using a single selected cell as the default Find value, though I am used to achieving the same thing with Copy/Paste, using F2 to access the formula if that's what I want.
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).
User avatar
MrProgrammer
Moderator
Posts: 4895
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Changes to Calc Find & Replace dialog

Post by MrProgrammer »

Some might be interested in a new Search In option: Formatted Value. This could, for example, find the "$" in a cell formatted as currency for English (USA) locale or the month abbreviation in a date formatted as YYYY-MMM-DD. Another Open Office innovation? Replacing formatted values would presumably go through the standard data entry routines that are used when values are typed by the user. As a programmer myself, I can imagine that this is the type of idea which may be more difficult to implement than it first seems, so I'll leave this suggestion up to the development team.
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).
dacha
Posts: 10
Joined: Fri Jul 15, 2011 10:18 am

Re: Changes to Calc Find & Replace dialog

Post by dacha »

MrProgrammer wrote:Some might be interested in a new Search In option: Formatted Value. This could, for example, find the "$" in a cell formatted as currency for English (USA) locale or the month abbreviation in a date formatted as YYYY-MMM-DD. Another Open Office innovation? Replacing formatted values would presumably go through the standard data entry routines that are used when values are typed by the user. As a programmer myself, I can imagine that this is the type of idea which may be more difficult to implement than it first seems, so I'll leave this suggestion up to the development team.
I already fixed that in SVN trunk, it should work in the 4.2.0 release. It was definitely not "difficult to implement": it was a one line patch :D:
r1712367 | damjan | 2015-11-03 20:44:21 +0200 (Tue, 03 Nov 2015) | 11 lines
Changed paths:
M /openoffice/trunk/main/sc/source/core/data/table6.cxx
A /openoffice/trunk/test/testgui/data/bvt/searchFormulasValues.ods
M /openoffice/trunk/test/testgui/source/bvt/gui/BasicFunctionTest.java
M /openoffice/trunk/test/testgui/source/testlib/gui/UIMap.java

#i60307# search for strings in date formats fails.
When "Search in" is set to "Values", search the formatted values
instead of just the raw input. This fixes a 9 year old
bug that's been duplicated at least 6 times, and matches what Excel
does. Tests show even "Replace" works, and works well: if
"October" is replaced by "November", it converts the original
month from 10 to 11; it only converts the cell type to text if the
resulting text is no longer a valid date. Also added BVT tests
for the expected behaviour.
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Changes to Calc Find & Replace dialog

Post by acknak »

dacha wrote:... I already fixed that in SVN trunk, ...
Nice! I always wondered why Calc wasn't searching the values as displayed.

Thank you!
AOO4/LO5 • Linux • Fedora 23
mandrill
Posts: 10
Joined: Mon Nov 28, 2011 12:00 pm

Re: Changes to Calc Find & Replace dialog

Post by mandrill »

Search in what? #106857 wants the default to be "Values" instead of the current "Formulas", but Excel (2007) uses "Formulas", so it might surprise users even though it is more natural.
Just because Excel does it doesn't make it right. If Excel included a vicious virus in it's code would you do the same?

I need to search in values far more often than I do in formulas (I use replace more often than find when editing formulas) and the default from the search box (not the F&R dialogue) should ALWAYS be to search in values not formulas.

How about adding a dialogue that allows the user to set these defaults up in accordance with how they use the software instead of just assuming that you, the developer, know best?
OOo 3.3.0 | Windows Vista Business 32bit SP2
Alex1
Volunteer
Posts: 723
Joined: Fri Feb 26, 2010 1:00 pm
Location: Netherlands

Re: Changes to Calc Find & Replace dialog

Post by Alex1 »

It would be nice if the More options setting was remembered during the session so we don't have to use it more than once.
The Current selection checkbox isn't necessary at all; just assume it if more than one cell is selected.
AOO 4.1.15 & LO 24.2 on Windows 10
Post Reply