I suggest that you try your hand at the two remaining tasks. Everything you need is in place and it only needs some additional work by you.
I am so new at this that I can’t make heads or tales of how to write it. Meaning, how to use SED to search and replace the text, and how to perform the same formatting for telephone numbers when the original number has periods between them.
HI KniazidisR.
Yes. I meant for the input to sed, which is a linefeed-delimited line editor.
Nigel. Your suggestion works great but has one issue in that empty cells are set to missing value.
I didn’t notice that… is there a workaround for that? But yes, if a cell is empty it returns “missing value”.
FWIW, I revised my earlier script to address all outstanding issues, including empty cells that showed missing value. The timing result with 8193 cells–all of which contained unformatted phone numbers–was 562 milliseconds on my 2023 Ventura Mac mini. This included a delay of 200 milliseconds prior to the GUI scripting portion of the script.
use framework "Foundation"
use scripting additions
set theColumn to "B"
tell application "Numbers" to tell table 1 of sheet 1 of document 1
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)^(\\d{3})(\\d{3})(\\d{4})$"
set theString to (theString's stringByReplacingOccurrencesOfString:thePattern withString:("($1)" & character id 160 & "$2-$3") options:1024 range:{0, theString's |length|()})
set theString to (theString's stringByReplacingOccurrencesOfString:"<null>" withString:"" options:1024 range:{0, theString's |length|()})
set the clipboard to (theString as text)
delay 0.2 -- test different values
tell application "System Events" to tell process "Numbers"
set frontmost to true
click menu item "Paste" of menu "Edit" of menu bar 1
end tell
-- delay 0.2 -- this and following line if desired
-- tell application "Numbers" to tell table 1 of sheet 1 of document 1 to set selection range to range (theColumn & "1:" & theColumn & "1")
So I see where you deal with this in the text search/replace since you’re already replacing other items, but in this script all that’s happening is reformatting the existing list, so I’m not sure what to put where in order to get the same protection from blank cell errors?
tell application "Numbers" to tell table 1 of sheet 1 of document 1
set format of column "F" to text
set rowCount to row count
set selection range to range ("F7:F" & rowCount)
set oldValues to value of cells of range ("F7:F" & rowCount)
end tell
set ATID to AppleScript's text item delimiters
set AppleScript's text item delimiters to linefeed -- NB.
set oldText to quoted form of (oldValues as text)
set newText to do shell script "echo " & oldText & " | sed -E 's/^\\(?([0-9]{3})\\)?([0-9]{3})?([0-9]{4})$/(\\1)" & character id 160 & "\\2-\\3/g' "
set AppleScript's text item delimiters to ATID
set the clipboard to newText
tell application "System Events"
set frontmost of process "Numbers" to true
keystroke "v" using {option down, shift down, command down}
end tell
This works awesome and is super fast! I’m wondering why Numbers has to show the Updating Spreadsheet delay on pasting. Is there something you can do to speed that up to avoid this, or is it just a matter of processing speed on my machine.
If I could ask a modification, I have another Column I that needs the same treatment, but that data is mixed (unlike the column you worked on which is all numbers) between text and numbers that are formatted like 212.555.1212 (unlike these numbers that are like 2125551212).
So the objective would be to leave all the text alone and only change the numbers.
Can this be added to the same script?
@pavilion. I do not get the spreadsheet delay on pasting, and I don’t know any method to make that faster.
The pattern in my script can be modified to work with both 1234567890 and 123.456.7890 cell contents. If the cell contents are not one of these, the cell contents are not changed (at least in my testing). My script is column based, and the simplest approach to format two columns is to make the existing script into a handler and to call it twice. Both of these changes are implemented in the following:
use framework "Foundation"
use scripting additions
formatColumn("A")
tell me to activate -- use delay instead of this and following line if desired
display dialog "Column A is being formatted. Select OK to format column B." with title "Phone Format"
formatColumn("B")
on formatColumn(theColumn)
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)^(\\d{3})\\.?(\\d{3})\\.?(\\d{4})$"
set theString to (theString's stringByReplacingOccurrencesOfString:thePattern withString:("($1)" & character id 160 & "$2-$3") options:1024 range:{0, theString's |length|()})
set theString to (theString's stringByReplacingOccurrencesOfString:"<null>" withString:"" options:1024 range:{0, theString's |length|()})
set the clipboard to (theString as text)
delay 0.2 -- test different values
tell application "System Events" to tell process "Numbers"
set frontmost to true
click menu item "Paste" of menu "Edit" of menu bar 1
end tell
end formatColumn
Wow, let me check it out and report back in a sec…
So the first part works fine, the 2125551212 to (212) 555-1212, but the new part doesn’t. The result of 212.555.1212 is -212 and nothing else. Also, all the text cells are truncated as well.
They are being truncated by the first space.
Also, rather than the dialog, if you could please just modify the script to run both processed consecutively without any input from the dialog, that would be much easier.
@pavilion. I retested my script with both Script Debugger and Script Editor and did not encounter the issues you mention. Could you post a screenshot of a sample spreadsheet, or perhaps another forum member will test the script.
Just for now, it’s best to retain the dialog. You can later replace those two lines with “delay 0.5” (no quotes).
BTW, I just made a small change to my script. I don’t think it will make a difference, but you might want to retest.
FWIW, my before and after spreadsheet screenshots. Before:
And, after:
Let me try a mixed group like you have… this time the numbers with the period look different. I haven’t changed anything in the script so that’s odd.
But when the column is only the dotted numbers (and other text), meaning to say that there’s no straight string of numbers, then it looks like this:
Thanks @pavilion for the screenshots. I tried to reproduce this issue with your examples but without success. I’m pretty sure the issue is with the regex pattern (which I’m not an expert on), and I’ll give this another look later on.
Before
After
Thanks so much, can’t wait to see the revision. Once you’re under the hood, please see my note above about the code generally.
What’s most interesting is that the results for same number type differ based on whatever else it finds in the column, which is something I didn’t expect and probably a clue on what’s happening to throw it off. You would think that each cell’s data is interpreted on its own but apparently no.