Thanks to this forum I've found out that it is possible to search and replace text by other text or formulas with AppleScript in Numbers 3.2
Does anybody have a script to search and replace a formula or a part of a formula by another formula or a partial formula.
For example:
I would like to be able to find:
= Table1::$b$3
and replace this by:
= Table1::$b$4
MacBook Pro (17-inch 2.4 GHz), OS X Mavericks (10.9.4)
This one should do what you want.
To use:
This does not work in Numbers versions prior to 3.2.
It's working here, but if you encounter unexpected behavior please post.
If your replacing entire formulas I think you will need to enter the leading = in the second dialog. You don't need to do this when replacing partial formulas.
SG
--Select cells in Numbers 3, run; find-replaces in formulas in that selection only
--https://discussions.apple.com/thread/6497878
--SGIII, v1, 201408
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
set f to display dialog "Find this in selected cells in Numbers " default answer "" with title "Find-Replace Step 1" buttons {"Cancel", "Next"}
set f to result's text returned
if f = "" then display dialog "Did you really want to replace null with something that will appear between each pair of letters?" buttons {"Cancel", "Yes"} with title "Find and Replace"
display dialog "Replace '" & f & "' with " default answer "" with title "Find-Replace Step 2"
set r to result's text returned
tell sr to repeat with i from 1 to count cells
try
set oVal to (cell i's formula) as text
if oVal is not "missing value" then
set nVal to my findReplace(oVal, f, r)
set value of cell i to nVal
end if
end try
end repeat
end tell
end tell
on error
display dialog "Did you select cells?" buttons {"cancel"} with title "Oops!"
end try
--handlers
to findReplace(tt, f, r)
set oTID to AppleScript's text item delimiters
considering 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 considering
set AppleScript's text item delimiters to oTID
return tt
end findReplaceThis one should do what you want.
To use:
This does not work in Numbers versions prior to 3.2.
It's working here, but if you encounter unexpected behavior please post.
If your replacing entire formulas I think you will need to enter the leading = in the second dialog. You don't need to do this when replacing partial formulas.
SG
--Select cells in Numbers 3, run; find-replaces in formulas in that selection only
--https://discussions.apple.com/thread/6497878
--SGIII, v1, 201408
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
set f to display dialog "Find this in selected cells in Numbers " default answer "" with title "Find-Replace Step 1" buttons {"Cancel", "Next"}
set f to result's text returned
if f = "" then display dialog "Did you really want to replace null with something that will appear between each pair of letters?" buttons {"Cancel", "Yes"} with title "Find and Replace"
display dialog "Replace '" & f & "' with " default answer "" with title "Find-Replace Step 2"
set r to result's text returned
tell sr to repeat with i from 1 to count cells
try
set oVal to (cell i's formula) as text
if oVal is not "missing value" then
set nVal to my findReplace(oVal, f, r)
set value of cell i to nVal
end if
end try
end repeat
end tell
end tell
on error
display dialog "Did you select cells?" buttons {"cancel"} with title "Oops!"
end try
--handlers
to findReplace(tt, f, r)
set oTID to AppleScript's text item delimiters
considering 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 considering
set AppleScript's text item delimiters to oTID
return tt
end findReplaceThanks a lot SGIII,
You just saved me a few hours of altering cells.
I did however replace the handlers block to be able to find & replace a part of formule, wich - as far as I experienced (maybe I missed something) - wasn't possible with the original 'handlers'-block.
(*
Select cells in Numbers 3, run; find-replaces in formulas in that selection only
https://discussions.apple.com/thread/6497878
SGIII, v1, 201408
*)
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
set f to display dialog "Find this in selected cells in Numbers " default answer "" with title "Find-Replace Step 1" buttons {"Cancel", "Next"}
set f to result's text returned
if f = "" then display dialog "Did you really want to replace null with something that will appear between each pair of letters?" buttons {"Cancel", "Yes"} with title "Find and Replace"
display dialog "Replace '" & f & "' with " default answer "" with title "Find-Replace Step 2"
set r to result's text returned
tell sr to repeat with i from 1 to count cells
try
set oVal to (cell i's formula) as text
if oVal is not "missing value" then
set nVal to my findReplace(oVal, f, r)
set value of cell i to nVal
end if
end try
end repeat
end tell
end tell
on error
display dialog "Did you select cells?" buttons {"cancel"} with title "Oops!"
end try
--handlers
on findReplace(oVal, f, r)
set AppleScript's text item delimiters to the f
set the item_list to every text item of oVal
set AppleScript's text item delimiters to the r
set oVal to the item_list as string
set AppleScript's text item delimiters to ""
return oVal
end findReplace
(*
to findReplace(tt, f, r)
set oTID to AppleScript's text item delimiters
considering 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 considering
set AppleScript's text item delimiters to oTID
return tt
end findReplace
*)Would it be possible to ignore a part of the search string?
I would like to ignore character -3 so that it would be possible to replace the following formulas:
Table 1::$B$3
Table 1::$D$3
by
Table 1::$B$4
Table 1::$D$3
with one single action.
So I only want to change the referenced rows, not the columns (or - if possible - vice versa).
I use formulas like:
IF Table 1::$B$3 = Table 2::$A$1; Use Table 1::$C$3;
IF Table 1::$B$3 = Table 2::$A$2; Use Table 1::$D$3;
...;
ELSE DoNothing
Hi,
I'm having trouble understanding what you mean by ignoring the 3. Could you explain a little more what you are looking for?
The original findReplace() handler should handle partial formulas. As written, however, it is case sensitive, which may explain why it didn't do what you expected. If you don't want it to be case sensitive you can remove the 'considering case' and 'end case' lines.
SG
First: the original script indeed works for parts of formula, I did leave the case consideration out of it, this part was probably causing the "problem" as you suggest.
Now, in Numbers-terminology, I would like to ignore the columns in the - I think - oVal and nVal blocks of the script.
(The columns are the third last characters (character -3) in my formulas, marked red in my previous post)
That way I would be able to change all the references to rows in a sheet at once,
For now I have to tell the script:
1: Change every value refering to (Table 1 :: Cell A1) to the value of (Table 1 :: Cell A2) but don't change the value refering to (Table 2 :: Cell A1)
2: Change every value refering to (Table 1 :: Cell B1) to the value of (Table 1 :: Cell B2) but don't change the value refering to (Table 2 :: Cell A1)
3: Change every value refering to (Table 1 :: Cell C1) to the value of (Table 1 :: Cell C2) but don't change the value refering to (Table 2 :: Cell A1)
...
As I would like to tell the script:
1: Change every value refering to (Table 1 :: Cell *1) to the value of (Table 1 :: Cell *2)
Period.
I can't find a so called 'wildcard character' in the Applescript's Language Guide, maybe Applescript uses a another term for these characters.
I see what you mean: a wildcard.
That can be done with 'do shell script' and 'regular expressions'. But we need someone like Hiroto to demonstrate how to do that.
SG
De Signature,
Would this approach work for you...
This is a very neat way edit a complex formula in a single cell....
a) Copy and Paste the formula to a new black page in Pages,
b) Do your Find and Replace, then
c) Copy and Paste the formulae back into the cell in Numbers.
It works like a charm.
Here is an example of how I used this approach...
Original cell formulae:
=AVERAGE(
SMALL(OFFSET(K3,1,0,20,1),1),SMALL(OFFSET(K3,1,0,20,1),2),
SMALL(OFFSET(K3,1,0,20,1),3),SMALL(OFFSET(K3,1,0,20,1),4),
SMALL(OFFSET(K3,1,0,20,1),5),SMALL(OFFSET(K3,1,0,20,1),6),
SMALL(OFFSET(K3,1,0,20,1),7),SMALL(OFFSET(K3,1,0,20,1),8))
Final version of formulae:
=AVERAGE(
SMALL(OFFSET(K3,N1,0,20,1),1),SMALL(OFFSET(K3,N1,0,20,1),2),
SMALL(OFFSET(K3,N1,0,20,1),3),SMALL(OFFSET(K3,N1,0,20,1),4),
SMALL(OFFSET(K3,N1,0,20,1),5),SMALL(OFFSET(K3,N1,0,20,1),6),
SMALL(OFFSET(K3,N1,0,20,1),7),SMALL(OFFSET(K3,N1,0,20,1),8))
Find Replace: be cautious with the Find, to ensure it is unique, in my this example ",1,", to avoid changing the" ,1)"
Enjoy...
This script really helped me. I've added it to my Numbers folder of the script menu. Thank you very much.
I did have one small problem, though. When I used it with merged cells, it split the cells and copied the new formula to both cells.
I don't know if anything can be done about this, but I thought you might be interested.
Glad to hear you've found the script helpful.
I don't think there is much that can be done about merged cells, except I would advise in general don't use merged cells!
Merged cells may look nice, but they're trouble. They can cause all kinds of mysterious, hard-to-trace problems.
It is almost always possible to achieve the same visual effect without them.
SG
Thanks, SGIII, you just saved me a ton of time!!!
Glad to hear you've found the script helpful!
SG
Search and replace formulas with AppleScript
