Format Telephone Number in Numbers

Scripting Forums AppleScript | Mac OS X
#42

Am getting this formatted result: 212)_555-1212

#44

Apologies, getting this result: (212)_555-1212. So the paste works but keeps the _ where it should be a space.

#45

Is the space essential or could you use for example a dash instead, like 123-456-7890?

#46

Well… that would be second best but the space would be ideal. Let me ask you… why is numbers doing this? Does it see a space as a column delimiter?

Because your code might be able to set a different delimiter just to get the data in, and then switch it back to the default, no?

set ATID to AppleScript's text item delimiters

#47

The problem with white space when pasting data is hardly possible to fix cleanly. Because it is hard-coded by the application developer.

I’ll try to go back to my first approach.
The column format is easy to remember and restore. It will be a little more difficult to limit the SED command to the 1 column, but there is one thought. I’ll need time.

#48

The code you posted actually works great and is very fast, except for the issue of the space. It pastes everything back in very neatly. Just trying to figure out how to get the space back.

#49

The paste action seems partially to behave like an import of CSV data. So it treats the space as column separator (for no good reason, imho).

Setting the target column’s format to text doesn’t change that. And I’m using JavaScript, which fortunately has no text item delimiters

#50

Okay great, thanks. Would love to also see if it can do the search/replace as well… will wait to see more!

#51

One more thing… the problem with copying/pasting all the data, rather than the one column, is that it takes a long time to paste it all back, delaying the process by nearly a minute on large files. What makes this method so cool is that it’s nearly instant when dealing with just the one column.

#52

Yeah, I’m seeing this when even pasting manually…

(212) 555-1212
(212) 555-1212
(212) 555-1212
(212) 555-1212
(212) 555-1212

it splits them into two cells. it’s the return character that’s bothering Numbers, not the space because pasting only one number works fine.

#53

Check this out guys… just doing this manually shows me that it’s not every return that’s causing the problem, just the first one.

So if I paste this in, putting a comma only on the first number, it works. Try it manually by pasting this into a cell.

(212) 555-1212,
(212) 555-1212
(212) 555-1212
(212) 555-1212
(212) 555-1212

#54

Why not (as a last resort workaround) add a comma to the end of the first record after copying the column (or add it to the cell before copy) then do the entire paste leaving that comma at the end of the first cell, and delete it when done?

UPDATE: yeah that won’t work because it’s looking for 10 digits, so the comma needs to be added after the formatting is done and before the paste.

#55

I just ran the script adding back the space and putting a comma at the end of the first cell, and it works like a charm. So all that’s needed is to do is strip the comma at the end.

Also, the column this script needs to be working on is Column F and the range should start at F7.

Two other adjustments.

One, is that I have another column (Column I) that’s populated by both text and phone numbers in this format: 212.555.1212, and would like to do the same formatting on them as well. So I’m not sure if you have ti look for a string length (which would be a problem) or could just look for the first character being a number, which would be perfect.

Second, is the search/replace columns (Column H, search for “In” and replace with “Incoming” and search for “Out” and replace with “Outgoing”) and (Column G, search for “Messaging” and replace with “Text”). If we can run a similar SED command on those as well and paste back the results, I imagine it will be much quicker than what I have now natively in Applescript.

#56

Hi.

  1. If you’re using sed, you should use a linefeed instead of a return as the delimiter when coercing the original values to text.
  2. I’ve had some success in the pasting by using a non-break space (character id 160) instead of an ordinary one:
tell application "Numbers" to tell table 1 of sheet 1 of document 1
	set format of column "B" to text
	set rowCount to row count
	set selection range to range ("B1:B" & rowCount)
	set oldValues to value of cells of range ("B1:B" & 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
delay 1

tell application "System Events"
	set frontmost of process "Numbers" to true
	keystroke "v" using {option down, shift down, command down}
end tell
1 Like
#57

Let me try it and see what happens!

#58

Bingo! Works great!

Can you take a stab at the other two issues above?

#59

The do shell script command alters the line endings to return. On my Catalina, Numbers.app works with return as well as with linefeed

#60

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.

#61

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.

#62

HI KniazidisR.

Yes. I meant for the input to sed, which is a linefeed-delimited line editor.