How to actually find something with Find in Calc?

users
#1

Hi!
Since the old find tool was replaced with the new extremely simplified one
I always have to use the Find & Replace dialogue to actually find what I'm
looking for.

So either the new (I know, it's not THAT new, but at least newer than the
old one…) find tool is useless or I don't understand how to use it,
probably the latter.

So let's do an example:
I have a sheet with a column (F in this case) with values formatted by just
clicking the % button, so it looks like this:
⁝
39,1%
38,8%
38,6%
⁝
(In my country we use a decimal comma, so if you use a decimal point, just
replace all commas with points in your mind…)
These are calculated values, for instance F100=IF(G100="";"";E100/G100)

Now I want to find a cell in the F column that contains the value 36,88. I
can easily do that with the Search & Replace dialogue:
Search: 36,88
Replace:
☐ Current selection only
☐ Regular expressions
☐ Similarity search
☐ Replace backwards
☐ Cell styles
☐ Wildcards
☐ Diacritic-sensitive
Direction: ⦿ Rows ⦾ Columns
Search in: Values

Hitting Find Next immediately finds and selects F109 in this specific case.

Using the overly simplified Find, this doesn't seem to be possible. It
seems like ”Search in formulas” is the default and unchangeable option
here, and how often do I want that? Only when I want to quickly edit
formulas in multiple cells at once, and then I would need the Search &
Replace dialogue anyway, wouldn't I?

So my question seems to end up being: What is the Find tool for? When is it
a better tool than the Search & Replace dialogue?

Kind regards

Johnny Rosenberg

#2

@Johnny,

Unless I am really misunderstanding, the "Formatted Display" check box will
shift search results to match against the *values* as formatted to cell. A
generalized implementation of the Find & Replace dialog's "Search in"
listbox selection.

Should give you your find results via the Find toolbar.

#3

@V Stuart
I thought that also - but that isn't working, as I just proved to myself,
with LO 6.2.0.1 RC1 (under Ubuntu 18.04.1) at least.

#4

Well, just to be clear - tried this with the example spreadsheet here with
version 6.0.7 and 5.4.7 and using FIND does not do what the original poster
is looking for. It seems the only way to find all of those values is by
using FIND & REPLACE.

Now I'm not sure what 'FORMATTED DISPLAY' actually does.

#5

drew-gmail wrote

Well, just to be clear - tried this with the example spreadsheet here with
version 6.0.7 and 5.4.7 and using FIND does not do what the original
poster
is looking for. It seems the only way to find all of those values is by
using FIND & REPLACE.

Now I'm not sure what 'FORMATTED DISPLAY' actually does.

Hmm, that's weird because it works on Windows builds (6.1.4.2 and current
6.3.0 master)--finding against values, not formulas--when "Formatted
Display" is checked enabled.

OS dependent somehow?

#6

V Stuart Foote wrote

Hmm, that's weird because it works on Windows builds (6.1.4.2 and current
6.3.0 master)--finding against values, not formulas--when "Formatted
Display" is checked enabled.

OS dependent somehow?

Bah, never-mind. Once I plugged in some formulas that calculate a
result--those results are *not* found on search from the Find toolbar. And
if the search string is a part of the formula it is found.

E.g. cell value entry of 31.5, a fomula entry of =31.5*2 and a formula
entry of =63/2

The first two are found on search, the third is not. The "Formatted
Display" checkbox has no affect.

Windows 6.1.4.2 and master 6.3.0

#7

Knew this had come up, with open BZ issues to correct:

tdf#102506 <https://bugs.documentfoundation.org/show_bug.cgi?id=102506>
... add Expert configuration option to set search for cell Values in stead
of Formulas

tdf#102615 <https://bugs.documentfoundation.org/show_bug.cgi?id=102615>
... Have Find Bar (Ctrl+F) and Find & Replace (Ctrl+H) search Values by
default

Think issue was to not change defaults until they could be recorded to user
profile--UI or Expert Configuration

At the moment with the OpenGrok git reindex running for CORE I'm unable to
figure out what "Formatted Display" checkbox does.

#8

Thanks for finding those issues - I guess lots of folks thinking the same
thing. I tried searching the expert configuration looking for any option
that might change the behavior also.

Also searched the on-line help and the last version of the Calc guide and
found zero entries for "Formatted Display". HMM, but given I'm supposed to
be helping update that guide I suppose I know who can change that, and I
will as soon as I can figure out what it is supposed to actually do :wink:

For whatever it is worth I would happy if the following could be coded:
Allow the setting of the option on the "Find & Replace" dialog under
search[ Formulas, Values, Notes] to stick and to influence the way the
"Find" toolbar searches.

Off to see about adding a comment to the first issue on that, if that
hasn't been suggested yet.

Best wishes,

Drew
Drew

#9

Thank you all for trying to find the solution to this issue. I also think
”Formatted Display” is a bit cryptic and I couldn't find out what it
actually does either.

Kind regards

Johnny Rosenberg