FWIW, I added a search-and-replace function to my script. The timing result on my 2023 Mac mini with one column and 5120 cells was 390 milliseconds.
-- revised 2023.07.06
use framework "Foundation"
use scripting additions
set spreadsheetColumn to "A"
set searchReplaceWords to {{"in out", "ingoing outgoing"}, {"in", "ingoing"}, {"out", "outgoing"}} -- place multi-word search items first
formatColumn(spreadsheetColumn, searchReplaceWords)
on formatColumn(theColumn, searchReplaceWords)
tell application "Numbers" to tell table 1 of sheet 1 of document 1
set format of column theColumn to text
set theValues to value of every cell in column theColumn
set selection range to range (theColumn & "1:" & theColumn & "1")
end tell
set theArray to current application's NSArray's arrayWithArray:theValues
set theString to theArray's componentsJoinedByString:linefeed
set thePattern to "(?m)^<null>$" -- empty cells
set theString to (theString's stringByReplacingOccurrencesOfString:thePattern withString:"" options:1024 range:{0, theString's |length|()})
set thePattern to "(?m)^(\\d{3})\\.?(\\d{3})\\.?(\\d{4})$" -- telephone numbers
set theString to (theString's stringByReplacingOccurrencesOfString:thePattern withString:("($1) $2-$3") options:1024 range:{0, theString's |length|()})
repeat with aList in searchReplaceWords
set thePattern to "(?i)\\b" & (item 1 of aList) & "\\b" -- search words
set theString to (theString's stringByReplacingOccurrencesOfString:thePattern withString:(item 2 of aList) options:1024 range:{0, theString's |length|()})
end repeat
set theString to (theString's stringByReplacingOccurrencesOfString:space withString:(character id 160) options:1024 range:{0, theString's |length|()}) -- nonbreaking spaces to prevent cell splitting
set the clipboard to (theString as text)
tell application "System Events" to tell process "Numbers"
set frontmost to true
click menu item "Paste and Match Style" of menu "Edit" of menu bar 1
end tell
end formatColumn
This script is identical to that above except that it uses the workaround suggested by chrillek to prevent cell splitting. The timing result with the same spreadsheet as above was 500 milliseconds.
use framework "Foundation"
use scripting additions
set spreadsheetColumn to "A"
set searchReplaceWords to {{"in out", "ingoing outgoing"}, {"in", "ingoing"}, {"out", "outgoing"}} -- place multi-word search items first
formatColumn(spreadsheetColumn, searchReplaceWords)
on formatColumn(theColumn, searchReplaceWords)
tell application "Numbers" to tell table 1 of sheet 1 of document 1
set format of column theColumn to text
set theValues to value of every cell in column theColumn
if item 1 of theValues is missing value then set item 1 of theValues to character id 160
set item 1 of theValues to item 1 of theValues & ","
set selection range to range (theColumn & "1:" & theColumn & "1")
end tell
set theArray to current application's NSArray's arrayWithArray:theValues
set theString to theArray's componentsJoinedByString:linefeed
set thePattern to "(?m)^<null>$" -- empty cells
set theString to (theString's stringByReplacingOccurrencesOfString:thePattern withString:"" options:1024 range:{0, theString's |length|()})
set thePattern to "(?m)^(\\d{3})\\.?(\\d{3})\\.?(\\d{4})$" -- telephone numbers
set theString to (theString's stringByReplacingOccurrencesOfString:thePattern withString:("($1) $2-$3") options:1024 range:{0, theString's |length|()})
repeat with aList in searchReplaceWords
set thePattern to "(?i)\\b" & (item 1 of aList) & "\\b" -- search words
set theString to (theString's stringByReplacingOccurrencesOfString:thePattern withString:(item 2 of aList) options:1024 range:{0, theString's |length|()})
end repeat
set the clipboard to (theString as text)
tell application "System Events" to tell process "Numbers"
set frontmost to true
click menu item "Paste and Match Style" of menu "Edit" of menu bar 1
end tell
tell application "Numbers" to tell table 1 of sheet 1 of document 1
set firstValue to value of cell (theColumn & "1")
set value of cell (theColumn & "1") to text 1 thru -2 of firstValue
end tell
end formatColumn