Numbers: how to remove phone number formatting

Question

Level 1

0 points

Numbers: how to remove phone number formatting

Is there any way to remove phone number formatting from a cell in Numbers?

I have nearly 100 separate files of contact information. I can bring the files in from a text delimited file (Excel, cvs, etc.)

The phone numbers in those files are formatted in every way imaginable (999) 999-9999, 999-999-9999, 999.999.9999, etc. I'd like to strip that formatting out so I get the just the numbers: 9999999999. My intent is just to use Numbers temporarily to strip out the phone number formating before bringing the data into the 4D database program.

Someone is selling a $29 formula for Excel to do this, but then I'd have to buy Excel again--my old version of Excel doesn't work in my latest version of the Mac OS. I can strip the phone number formatting out in 4D, but I'm not a programmer and it is complicated.

Posted on Jul 24, 2012 1:10 PM

Reply

Answer 1

Best reply

Wayne Contello

Level 7

26,529 points

Jul 24, 2012 2:12 PM in response to MikeM.

Manually:

search for "-" and replace with nothing

search for "(" and replace with nothing

search for ")" and replace with nothing

search for "." and replace with nothing

You could also make a table that allows you to paste in the phone numbers in their various forms and have a cell with a formula that replces the offending characters with nothing:

B1=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,".",""),")",""),"(" ,"")," ",""),"-","")

this repeatedly substitutes various characters in your offending list (".", "-", " ", "(", ")") with nothing.

The last way is to use a script of some kind to make the substitution.

Reply

Answer 2

Jul 24, 2012 2:50 PM in response to Wayne Contello

here is a shell script which can take an input file (phoneNumbers.txt) and remove characters then store to a new file (fixed_phoneNumbers.txt):

tr -d "\- \.()" < phoneNumbers.txt > fixed_phoneNumbers.txt

I will look later how to pass in a list of files

Reply

Answer 3

Barry

Level 9

59,861 points

Jul 24, 2012 2:52 PM in response to Wayne Contello

One item missing in Wayne's Find/Replace list:

Find " " (one space), replace with nothing.

That case is accounted for this the SUBSTITUTE formula provided.

Regards,

Barry

Reply

Answer 4

Jul 24, 2012 3:11 PM in response to Barry

Original formula had single space substitution (highlighted):

shell script also contains immediately after "\-"-- you don't have to escape space:

tr -d "\- \.()" < phoneNumbers.txt > fixed_phoneNumbers.txt

Reply

Answer 5

Barry

Level 9

59,861 points

Jul 24, 2012 3:39 PM in response to Wayne Contello

Bad editing on my part, Wayne. Here's a correction to the misleading part:

That case is accounted for this in the SUBSTITUTE formula provided.

It's the "Manually" list that's missing 'search for " " (space) and replace with nothing' step.

Regards,

Barry

Reply

Answer 6

MikeM. Author

Level 1

0 points

Jul 24, 2012 3:48 PM in response to MikeM.

Maybe I have a simpler way to explain this: I want to remove anything that's not a number inside a column, without affecting the other columns.

Reply

Answer 7

Jul 24, 2012 4:22 PM in response to MikeM.

I think you would need to provide the general format of the data like:

- how many columns

- how the columns are delimited

the list of characters you want to remove before we can help. Since there is 100 files I think a shell script (or applescript) is the best solution.

I demonstrated how to modify one column using Numbers you can simply reference the other columns directly like:

In the bottom table enter the following formulas:

A1=Original data :: A1

B1=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Original data :: B1,".",""),")",""),"(","")," ",""),"-","")

C1=Original data :: C1

D1=Original data :: D1

select A1 thru D1 and fill down as needed

Copy modified data out of the bottom table (shrewdly named "Modified only phone Numbers"), paste into a new table by using the menu item "Edit > Paste Values".

Then paste new data in the top table and repeat

Reply

Answer 8

MikeM. Author

Level 1

0 points

Jul 24, 2012 9:09 PM in response to Wayne Contello

Wayne, I'm blown away that you spent so much time helping on this. Really blown away.

Here's some more questions:

--On the search and replace, is there any way to limit the search area to one column?

--I wrote up that simplified post before I realized you had replied and then it took me a little while to go through what you did to see if it would work for me. The formula definitely works. Well, I should say, I can get it to work with one cell. What I can't seem to figure out how to apply it to multiple rows or apply it the whole column. (If I select muliple rows--say B1 to B6, I'm no longer able to enter a function.) I think you're trying to explain it in the 4:22 pm post, but I don't quite get it.

By the way, the files will come in once a week or so, from different sources, so I won't know what I'm getting until each file arrives. I'm just trying to figure this out now so I do the work quicker when it comes in. Each file will probably have 400-1200 records, with 3 sets of phone numbers (home, work, and mobile) to change.

Reply

Answer 9

MikeM. Author

Level 1

0 points

Jul 24, 2012 10:09 PM in response to MikeM.

If this helps, here is one of my attempts to have it apply to the whole column:

=B1:B487=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1:A487,".",""), ")",""),"(" ,"")," ",""),"-","")

Unfortunately it returned an orange triange / syntax errror.

Reply

Answer 10

Jul 24, 2012 10:47 PM in response to MikeM.

to apply to the whole column enter tha original formula in cell B1 as:

B1=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,".",""), ")",""),"(" ,"")," ",""),"-","")

then select A1, then grah (click and hold the little circle at the bottom right of the selection) and drag straight down to fill the formula as needed, then release te mouse button. Numbers will adjust the cell reference automatically.

The other method is to enter the formula in B1 as described above. Then select B1, then copy, then select B2, the shift click the and of column B, then paste.

Reply

Answer 11

Barry

Level 9

59,861 points

Jul 24, 2012 11:40 PM in response to Wayne Contello

"The other method is to enter the formula in B1 as described above. Then select B1, then copy, then select B2, the shift click the and of column B, then paste."

Even easier, if you want to fill the formula into the whole column is to:

Enter the formula in B1 as described, then select B1 and Copy.

Then click the column B reference tab to select the whole column, then paste.

Regards,

Barry

Reply

Answer 12

MikeM. Author

Level 1

0 points

Jul 25, 2012 10:13 AM in response to Barry

AWESOME. I think what was tripping me up was not thinking to use the graphical copy and paste. When I didn't see the ability to type in function area, I didn't think it could be done. (It's always the simplest of things that trip you up). Thank you SO much.

Reply