find 9999 vs 9,999 (thousands separator)

I do not know how to search for formatting. You can select a column and apply formatting but I not sure to locate cells formatted a particular way? Am I misunderstanding your question?

What you are going to do once you located (assuming you could) cells formatted a particular way?

ZZ,

Numbers is built on the premise of WYSIWYG, regardless of what is behind the display, be that a calculation or a different format or lack of format. For this reason, I think, you will always find stuff as it is displayed, with no other option.

Jerry

Numbers does have several functions that accept wild cards:

Function, Accepts Numeric Comparison, Accepts Wild Cards...

Beyond that, you can always choose another app.

Jerry

Hi zamzmith,

Let me say it another way. I think preforming a find for 9999 should find both 9999 and 9,999.

If I understand correctly what you are trying to do, this is really easy to do with Numbers 3. Just use functionality built into every Mac.

Paste this into AppleScript Editor, click any cell in the table you want to search, and run:

-- Find a number in the selected table, regardless of formatting

set findValue to display dialog "Find first occurrence of this value " default answer "" buttons {"OK"} default button "OK"

set findValue to (text returned of result) as number

tell application "Numbers" to tell front document to tell active sheet to tell (first table whose class of selection range is range)

repeat with i from 1 to count cells

tell cell i

try

set aValue to its value as number

on error

set aValue to "" as number

end try

set anAddress to its name

end tell

if findValue = aValue then

set the selection range to range anAddress

return

end if

end repeat

end tell

-- end script

You can turn this into an Automator Service and assign a keyboard shortcut if you need to do this kind of search a lot.

SG

I think if you entered:

"9999" that is different than "9,999"

I did find a behavior I was not expecting...

I typed "9999" then formatted the cell to show the thousands separator. This is the same thing as having typed "9,999".

The find dialog finds one match

The find dialog locates two matches:

You can post feedback to Apple regarding using the menu item "Numbers > Provide Numbers Feedback"

If there is a larger context perhaps explaining that would help.

..You don't think a spreadsheet program should be able to Find numbers, or ignore it's own automatic formatting?

Not necessarily.

I

Zam,

I think it would be nice if Numbers searched on the value "9999" the same as "9,999" (Libre Office does, I checked). However I also like that I can search literally on what I type. I think you should select the column where you are entering and reviewing numeric values and force the format as you need it so that is consistent.

Select the column, then use the cell formatter to set the format:

Inconsistent formatting:

After selecting a format for the column:

You can argue I am "band-aiding" a problem (the search) but I argue I am using good practice.

Hope this helps,

Wayne

I think zamzmith is right. One should be able to search for a numeric value without worrying about formatting, just can be done in Excel and (I believe) many other spreadsheets.

But that point is pretty much moot, I think, because unless and until Apple gets around to enhancing Find (it would also be nice to be able to search within a selected range) one *can* do whatever is needed very easily, with a simple script attached to a keyboard shortcut. Takes just a minute or two to set up.

SG

Hello SG,

Unfortunately the problem is not necessarily that simple. It is common to have a cell formatted only to show certain decimal places where real value is different, in which case script needs to absorb the difference in comparison. In addition to it, "Find Next" feature is essential in real world searching.

* Find Next is relatively easy to implement but the rounding in formatting issue is very hard to handle and yet it must be handled correctly in order to avoid false negative results.

Regards,

H

Hiroto,

Wouldn't the easiest approach be to write a script that unifies the thousands separator format throughput the document? Then the built-in search tool's behavior wouldn't be an issue.

Jerry

Hello Jerry,

It would be so if Numbers.app provided decent AppleScript interface to get and set thousand separator setting for any given range just as Excel for Mac does or AppleWorks does. But Numbers does not and the only way to script the setting in Numbers would be to use GUI scripting which is messy and unreliable if possible.

Since Numbers is a spreadsheet programme, I think it should really implement decent numeric search method regardless of number format. Even ClarisWorks / AppleWorks can do.

Regards,

H

Thanks for your perspective on this. That puts this issue squarely in the Submit Feedback category.

Jerry