Format Telephone Number in Numbers

[peavine]

Nigel. Your suggestion works great but has one issue in that empty cells are set to missing value.

[pavilion]

I didn’t notice that… is there a workaround for that? But yes, if a cell is empty it returns “missing value”.

[Nigel_Garvey]

I dealt with that in pavilion’s other topic.

[peavine]

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")

[pavilion]

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

[pavilion]

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?

[peavine]

@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

[pavilion]

Wow, let me check it out and report back in a sec…

[pavilion]

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.

[pavilion]

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.

[peavine]

@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:

[pavilion]

Here’s what the cells look like after processing. I’m on Ventura if that matters.

[pavilion]

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.

[pavilion]

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:

[peavine]

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

[pavilion]

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.

[pavilion]

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.

[pavilion]

And just to finish off, here’s a screenshot of how it works fine when the numbers are plain strings, but only if they are by themselves in the column.

[pavilion]

I found a regex site that specifically discusses formatting 10 digit phone numbers and suggests using this pattern. I then tested it on one of the online regex test sites and both our patterns match, whereas when I tested yours, they didn’t. Maybe that’s where the problem lies.

^(\+\d{1,2}\s?)?\(?\d{3}\)?[\s.-]?\d{3}[\s.-]?\d{4}$

See here:

https://stackoverflow.com/questions/16699007/regular-expression-to-match-standard-10-digit-phone-number

[peavine]

@pavilion. I worked through the suggested regex pattern and most of what it matches does not apply in this case. Also, it lacks 3 required capture groups and does not enable multiline mode.

About the only thing it does differently is separators. The following is my regex pattern modified to use the same separators pattern. You can copy and paste this in my earlier script to see if it makes a difference. It does work in my testing, although my earlier regex pattern still works for me

set thePattern to "(?m)^(\\d{3})[\\s.-]?(\\d{3})[\\s.-]?(\\d{4})$"

FWIW, my understanding of the suggested regex pattern is:

^(\+\d{1,2}\s?)?\(?\d{3}\)?[\s.-]?\d{3}[\s.-]?\d{4}$

^ = start of line
( = begin capture group
\+ = match “+”
\d{1,2} = match 1 or 2 digits
\s? = match 0 or 1 whitespace characters
)? = end capture group - once or none
\(? = match 0 or 1 open parentheses
\d{3} = three digits
\)? = match 0 or 1 closing parentheses
[\s.-]? = march 0 or 1 whitespace or dot or dash
\d{3} = three digits
[\s.-]? = march 0 or 1 whitespace or dot or dash
\d{4} = 4 digits
$ = end of line

BTW, I did test my earlier regex pattern here and it worked as expected. I eliminated below the second backslashes for testing on this site:

(?m)^(\d{3})\.?(\d{3})\.?(\d{4})$