Format Telephone Number in Numbers

Scripting Forums AppleScript | Mac OS X
#126

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
#127

This thread appears finished, but I wanted to include a final note explaining why the cell splitting occursā€“at least on my Ventura computer.

My test spreadsheet was the one supplied by the OP in post 88. My test script was my first script in post 122, and I disabled the following line to force cell splitting:

set theString to (theString's stringByReplacingOccurrencesOfString:space withString:(character id 160) options:1024 range:{0, theString's |length|()}) -- nonbreaking spaces to prevent cell splitting

After running the script with the spreadsheet open, cell splitting occurred as expected, and I get the following message:

Table data was imported and can be adjusted.

To adjust the table data, the user can click on the notification or can click on ā€œAdjust Import Settingsā€, which is intermittently (but not previously) available at the bottom of the ā€œTableā€ inspector sheet.

The Import Settings dialog has a number of options and the following will make the spreadsheet display as desired with no cell splitting:

  1. Click on the Delimited tab.
  2. Disable all options after the heading ā€œSeparate Values Usingā€ .
  3. Click on Update Table.

I retested the above procedure with a spreadsheet I created, which contained 5120 cells. The cell splitting occurred as expected, and it was corrected by adjusting the import settings.

Unfortunately, I do not know of a procedure to permanently change the import settings, and altering the import settings every time the script is run is clumsy at best. So, in the end, the two fixes are the same as before:

  • replace all regular spaces with non-breaking spaces, or
  • add a comma at the end of the entry in cell 1.
#128

Why is that necessary if you add the comma to the first line, too? That should already prevent cell splitting.

#129

Itā€™s not necessary. There are two fixes, and you use one or the other. Iā€™ll edit my post to clarify.

BTW, my first script in post 122 uses non-breaking spaces and my second script in post 122 uses a comma after the entry in cell 1.