8/17/13
8:50 am

Forum Question: How Can I Strip Parenthesis From a Phone Number?

I Numbers, how can I turn this:
(555) 123-4567
into this:
555.123.4567
This is the format we use when printing. I don’t care if I need to use an extraneous column that I can copy and paste the original format into as long as it automatically returns the new format in the cell I want.
Also… in a perfect world, I could copy and paste any format into this “extraneous” input cell and it would still reformat it. In other words, I can paste +1 (555) 123-4567 and it would still return 555.123.4567. or… 555-123-4567 and it gives me the output I want.
possible?
—–
Dan Atchison

Comments: 3 Responses to “How Can I Strip Parenthesis From a Phone Number?”

    8/17/13 @ 8:58 am

    Numbers really isn’t a text processing tool like that. A programmer working with a database would be able to do this easily using regular expressions. They would build it into the user interface for the database.
    But if you are using a spreadsheet for this kind of thing, then you are limited to only using functions in cells.
    Here’s one way to do this using the SUBSTITUTE function:
    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,”(“,””),”)”,”.”),”-“,”.”)
    A1 would be the cell with the original number in it. There are three SUBSTITUTES nested inside easy other, so it is messy. The inner one converts a ( to nothing to get rid of the opening parenthesis. The second one converts a ) to a . and the outer one converts a – to a .
    So it works for your first example. It would also work for your example that uses two dashes. You’l need to nest more for the other example, getting rid of the +1 and spaces.
    Or, you can use SUBSTITUTES to get rid of all characters that may appear that aren’t numbers, and then use REPLACE to insert periods after the 3rd and 6th digits. That gets very nested.
    It is difficult to deal with nested functions like this, so you may want to get the help of a programmer. If not, then try building them one function at a time. Test. Then nest inside the next function. Test. And so on. It can get frustrating.

    Dan
    8/19/13 @ 8:10 am

    Here’s an approach that is pretty basic. It uses an intermediate column. There may be prettier ways…

    The formula in the Intermedaite body cells is:

    =RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A, “(“, “”), “)”, “”), ” “, “”), “-“, “”), “+”, “”), 10)

    The formula in the Final body cells is:

    =IF(LEN(B)>0, MID(B, 1, 3)&”.”&MID(B, 4,3)&”.”&MID(B, 7,4), “”)

    If there are any other cases you need to cover, you can extend the concept of the intermediate calculation.

    Jim
    8/22/13 @ 9:17 am

    Hi Gary and Dan, You guys are great. This question should be put out to some kind of open test to see who can develop the shortest and easiest solution to this problem. Call it the “Substitute” challenge.

    Thanks

Comments Closed.