How to automate find and replace in numbers with a predefined list?
How can I automated find and replace in a Numbers file based on a predefined master list?
For example, I want to find and replace all:
- "ABC" to "XYZ"
- "123" with "789"
A few thoughts / workflow expectations:
- My find and replace routine has become burdensome as I manually run through 20+ find / replace actions
- Find and replace will be for the whole document, not limited to a certain column, row, cell
- I want to have running master list that I can add to
- I would like to open a file, run this script and have the find/replace done
- It would be great if I can incorporate a wildcard in the find
I'm not exactly sure how you have the Numbers document formatted or how exactly you want to store the Master List of Find & Replace items or where, however, the following example AppleScript code is set up to show the use of the Master List of Find & Replace items as a single table in a sheet in the Numbers document and is comprised of two columns and however many rows you need.
The images below show the sheets, the Master List of Find & Replace items and the before and after of sheet 1. The data is simplistic but the bottom line is it works to quickly find and replace the pairs of Find & Replace items in the Master List.
This will be so much faster than any manual process to accomplish the same goal.
Note that as coded, it assumes there are no formulas in the cells and they are formatted as text.
Example AppleScript code:
- As coded this works with just the active sheet in the front document.
tell application "Numbers"
tell front document
-- # Get the Find & Replace list.
set theFindReplaceList to ¬
the formatted value of ¬
the cells of the rows 2 thru -1 of ¬
table "Find & Replace" of ¬
sheet "Master List" whose ¬
value is not missing value
-- # Clean up the List removing empty cells and rows
-- # so there are only pairs of find/replace items.
set theFindReplaceListItems to {}
repeat with aListItem in theFindReplaceList
if the length of aListItem is 2 then ¬
copy aListItem to the end of theFindReplaceListItems
end repeat
copy theFindReplaceListItems to theFindReplaceList
-- ### Find & Replace ###
if the name of active sheet is not "Master List" then
-- # For each table of the active sheet.
repeat with i from 1 to the count of the tables of the active sheet
-- # For each Find & Replace pair.
repeat with thisListItem in theFindReplaceList
-- # For every cell containing the FIND text.
set theReplaceCellsList to the (cells of table i of sheet 1 ¬
whose formatted value contains item 1 of thisListItem)
-- # Replace it with the REPLACE text.
-- # Uncomment 'considering case' and 'end considering'
-- # for case senstive find/replace operations.
--considering case
repeat with aCell in theReplaceCellsList
set the value of aCell to my findAndReplaceInCellValue(formatted value of aCell, ¬
item 1 of thisListItem, item 2 of thisListItem)
end repeat
--end considering
end repeat
end repeat
tell table 1 of active sheet to set selection range to range "A1"
end if
end tell
end tell
-- ## Handler ##
on findAndReplaceInCellValue(theFormatedCellValue, theFindValue, theReplaceValue)
set AppleScript's text item delimiters to theFindValue
set theTextItems to every text item of theFormatedCellValue
set AppleScript's text item delimiters to theReplaceValue
set theFormatedCellValue to theTextItems as string
set AppleScript's text item delimiters to ""
return theFormatedCellValue
end findAndReplaceInCellValue
Notes:
While the example AppleScript code as coded is using a sheet within the target document for its Find & Replace items, nonetheless, it can be coded to use and external source, e.g., another Numbers document.
In either code examples, the Master List sheet is not touched by the find/replace operations.
The find/replace operations are case insensitive. If you need case sensitive find/replace then uncomment the considering case and end considering statements in the example AppleScript code.
As coded the find/replace operations act on cells containing the value of FIND text and anything else in the cell. If you want to limit to cells that have only the FIND text and nothing else, then:
Change:
set theReplaceCellsList to the (cells of table i of aSheet ¬
whose formatted value contains item 1 of thisListItem)
To:
set theReplaceCellsList to the (cells of table i of aSheet ¬
whose formatted value is item 1 of thisListItem)
The example AppleScript code can be used in an Automator Service/Quick Action using a Run AppleScript action and assigned a keyboard shortcut in System Preferences > Keyboard > Shortcuts > Services or used with any third-party application that can run AppleScript scripts, etc
Example AppleScript code:
- As coded this works with all sheets in the front document.
tell application "Numbers"
tell front document
-- # Get the Find & Replace list.
set theFindReplaceList to ¬
the formatted value of ¬
the cells of the rows 2 thru -1 of ¬
table "Find & Replace" of ¬
sheet "Master List" whose ¬
value is not missing value
-- # Clean up the List removing empty cells and rows
-- # so there are only pairs of find/replace items.
set theFindReplaceListItems to {}
repeat with aListItem in theFindReplaceList
if the length of aListItem is 2 then ¬
copy aListItem to the end of theFindReplaceListItems
end repeat
copy theFindReplaceListItems to theFindReplaceList
-- ### Find & Replace ###
-- # For each sheet in the document.
repeat with aSheet in (sheets whose name is not "Master List")
-- # For each table of the sheet.
repeat with i from 1 to the count of the tables of aSheet
-- # For each Find & Replace pair.
repeat with thisListItem in theFindReplaceList
-- # For every cell containing the FIND text.
set theReplaceCellsList to the (cells of table i of aSheet ¬
whose formatted value contains item 1 of thisListItem)
-- # Replace it with the REPLACE text.
-- # Uncomment 'considering case' and 'end considering'
-- # for case senstive find/replace operations.
--considering case
repeat with aCell in theReplaceCellsList
set the value of aCell to my findAndReplaceInCellValue(formatted value of aCell, ¬
item 1 of thisListItem, item 2 of thisListItem)
end repeat
--end considering
end repeat
end repeat
tell table 1 of active sheet to set selection range to range "A1"
end repeat
end tell
end tell
-- ## Handler ##
on findAndReplaceInCellValue(theFormatedCellValue, theFindValue, theReplaceValue)
set AppleScript's text item delimiters to theFindValue
set theTextItems to every text item of theFormatedCellValue
set AppleScript's text item delimiters to theReplaceValue
set theFormatedCellValue to theTextItems as string
set AppleScript's text item delimiters to ""
return theFormatedCellValue
end findAndReplaceInCellValue
Master List
The Find & Replace table must always be two columns, however, the script is coded to only process rows containing both a find and replace item.
Before:
After:
Note: The example AppleScript code is just that and sans any included error handling does not contain any additional error handling as may be appropriate. The onus is upon the user to add any error handling as may be appropriate, needed or wanted. Have a look at the try statement and error statement in the AppleScript Language Guide. See also, Working with Errors. Additionally, the use of the delay command may be necessary between events where appropriate, e.g. delay 0.5, with the value of the delay set appropriately.