How to search in selected area in Numbers

I use a regular internet search, not the useless "search" feature of this forum, to find posts here.

If you are saying that when you type a number into the "find" field of the script it tacks a 0 onto the end as you are typing, I have not seen that and have no explanation why it would. It is a text entry field. It does not check if something is a digit or any other character or if the entire string when done is a number or just text. Same with the "replace" entry field.

I have found where the problem is with numbers in cells. When getting the value of a cell, an integral number like 123 is received as 123.0 in the script. The script below uses the formatted value of the cell (i.e., what you see displayed) instead. Note that it will round numbers to the number of decimals currently displayed. The built-in find/replace appears to do the same thing.

I made it so it only changes cells that actually need a change. If nothing in a cell was found/replaced then it leaves it alone. So that rounding stuff I mentioned above will only occur to cells that had something found/replaced.

It ignores cells with formulas and those that are blank. The older version of SGIII's script was this way. I just put that back into it.

It highlights "Next" as the default button in the "find" dialog.

It no longer considers case. ABC is the same as Abc, aBc, and so on.

Copy the script from below and replace the one in the shortcut with it. Let me know if it does all you need it to do.

-- Numbers App Find&Replace in Selection
-- SGIII Author
-- 12/09/2024 Badunit edit 
-- 1) Reincorporate the check for formulas and missing values so those cells are not affected. 
-- 2) Use the formatted value of the cell for the find/replace. Prior to this, integral numbers such 
--  as 123 were being turned into 123.0. Note that this will round any decimal numbers to the number of
--  places being displayed, just as the built-in find/replace does.
-- 3) Reincorporated "next" as default button for the "find" dialog
-- 4) Changed it to ignore case vs consider case
-- 5) Set new values for cells only if they have changed.

set f to display dialog "Find this in selected cells in Numbers " default answer "" with title "Find and Replace Step 1" buttons {"Cancel", "Next"} default button "Next"
set f to text returned of result
if f = "" then display dialog "Did you really mean you want to replace null with something?  This will result in your replacement string being inserted between each pair of letters." buttons {"Cancel", "Yes"} with title "Find and Replace"

display dialog "Replace '" & f & "' with " default answer "" with title "Find and Replace Step 2"
set r to text returned of result

try
	tell application "Numbers" to tell front document to tell active sheet
		tell (first table whose selection range's class is range)
			set sr to selection range
			tell sr to repeat with i from 1 to count cells
				--if not a formula and not blank
				if cell i's formula is missing value and cell i's value is not missing value then
					set oVal to (cell i's formatted value)
					--return oVal
					set nVal to my findReplace(oVal, f, r)
					-- return nVal
					if (cell i's formatted value) ≠ nVal then
						set cell i's value to nVal
					end if
				end if
			end repeat
		end tell
	end tell
on error
	display dialog "Are the cells where you want to do a Find Replace selected?" buttons {"cancel"} with title "Oops!"
end try

--handlers
to findReplace(tt, f, r)
	set oTID to AppleScript's text item delimiters
	ignoring case
		set AppleScript's text item delimiters to f
		set lst to tt's text items
		set AppleScript's text item delimiters to r
		set tt to lst as string
	end ignoring
	set AppleScript's text item delimiters to oTID
	return tt
end findReplace

My memory is slipping. It has already been done. It was done this September by SGIII. Here is a link to the shortcut

https://www.icloud.com/shortcuts/7dbd4aefdc05498ba3fe9d077074e736

It is SGIII's script/shortcut. All I did was post a link to it. It does what you requested -> "search in selected area only". You are right it does not do find&replace. The first script I posted, also written by SGIII, does find&replace all "in a selected area only". If you need that, try again to download and install the first shortcut. What you posted in your screenshot was not it. Neither one highlights cells.

I'm not sure what you did because that is not the shortcut. The shortcut is entirely an AppleScript. But this is not what you were asking for anyway. My mistake. It finds and replaces, it won't stop on or highlight the "found" cells. I think it would be possible to edit it to make it stop on found cells and let you move to the next one using a mouse click or something. I don't know if SGIII will get around to that or maybe me or someone else and I don't know when it might happen.

The link is supposed to be to a Shortcut app shortcut, not a service. It is run via the Shortcuts app. You can put Shortcut app shortcuts up on the menu bar at the top of the screen for easy access. If the link from the other thread is giving you problems, here is a link to my copy of the shortcut.

EDIT: I'll post a link soon. I have two versions of the shortcut and I the link I provided might install both. I need to get rid of one of them.

If you open the Shortcuts app and look at the shortcut, do you see the Applescript that is the heart of this shortcut? What you posted awhile back was not it.

I just realized you are the OP from the other link, which was from a year ago. Did this not work then either?

Prior to the Shortcut app the way to do this was to create a service. SGIII provided a link to a service back then. You probably have it in your services menu from a year ago, not from anything you did recently.

I want to make a change to the shortcut version of this script. If you select empty cells for find&replace it will replace them with "missing value". The older script did not do that. I can't do it right now, though.

I believe somewhere in all those posts it said it was case sensitive. I'll look into it. One thing I noticed with numbers is it changes the formatting of whole numbers from "auto" decimal places to 1 decimal place and tacks on a ".0". Is it doing other things as well? It also changes blank cells into "missing value" which is something easily fixable.

The version you installed does find/replace within formulas, too, but that can be dangerous because it can find/replace letters within a function name and break the formula. I'm editing my version back to the original where it skipped over formulas.

Post some specific examples of things that are not working as expected and I can see what I can do. The list should contain the original value, find text, replace text, and final value, and what is wrong with the result.

It would be easier if you told me what actually happened vs saying "did not do the job" and leaving it up to me to figure out what you are seeing. I'm trying to help you here.

It works here. I tried it in a region that uses a dot as the decimal separator and also in one that uses comma, both in English. I tried replacing 22,22 with 2222. I also tried replacing the comma with nothing. Both worked properly. If you got the result 22.22 where the comma was replaced by a dot, the script that actually ran might be the old one that did not use the formatted value of the cell.

Bomiboll wrote:

As an example, a search for replacing a comma in 22,22 to 2222 did not do the job

22,22 converts correctly to 2222 here.

Are you sure that , is a real comma and not part of the number formatting in your region?

That script can't change the format you have chosen.

SG

Numbers: Find and Replace in selected area - Apple Community