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

enter image description here

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:

enter image description here


After:

enter image description here


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.