Tip: Formula to Format Telephone Numbers

by Michael on November 7, 2007

I just found this great formatting tip over on the Apple Numbers discussion boards. A poster had the following problem:

He had a series of phone numbers formatted as 10 digits (i.e. 5035551212). He wanted to re-format the numbers to read as (503) 555-1212.

5|=vv (who is also a frequent poster on our forums) provided the answer – with a little help from WWJD.

The solution is the following formula:

=”(“&LEFT(A2,3)&”) “&MID(A2,4,3)&”-”&RIGHT(A2,4)

Where cell A2 is the value to be converted.

Apple iWork Numbers Screen Shot

Let’s analyze the formula in three steps (as in the screen shot above, our value in cell A2 is 3349967444):

1) =”(“&LEFT(A2,3)&”)”

First, the = sign is used to let Numbers know that this is a formula. Then, “(“, inserts an open parentheses, it is enclosed in quotes to indicate to Numbers that it is text. Next, an Ampersand (&) is used as a shortcut for the CONCATENATE function. This function allows the user to link together text and function results into a string.

The next portion is LEFT(A2,3). The LEFT function retrieves a specified number of characters from the left end of a string. The format is LEFT(text, [length]). In this case the formula is referencing cell A2, and counting 3 spaces over. The value returned is 334.

Finally, the CONCATENATE function is used again, to link “) ” to the string. Notice that there is a space intentionally inserted after the close parenthesis, before the closing quote. The result of these operations is: (334) .

2) &MID(A2,4,3)&”-”

The next portion of the formula uses the MID function. The MID function extracts a specified number of characters from a string starting at a specified position. The format is MID(text, start, length). In the formula above, cell A2 contains the text, the starting position is 4, and the length is 3. How did 5|=vv know to use 4 as the starting position? He had already extracted the first 3 characters with the first portion of the formula (with the LEFT function), so to continue, he started with the next position in the string, 4. We need the need the next 3 numbers, so the length is 3.

Again the CONCATENATE shortcut (&) is used to join a hyphen to the string “-”. This section of the formula gives us: 996-. When we put this together with the results of the first formula, we get: (334) 996-. Almost there!

3) &RIGHT(A2,4)

If you have made it this far, you can probably infer what this last function does. The RIGHT function extracts a specified number of characters from the right end of a string. The format is RIGHT(text, [length]). Again, our text is in cell A2, and since we only need the last four characters of the string, the length is 4.

In our example above, the formula returns 7444.

Putting all three sections together, we get (334) 996-7444, which is the exact format that we desired.

Thanks 5|=vv.

——————————————————————————————————-

Do you have a cool tip or formula to share? Join us in the forums!

{ 4 comments… read them below or add one }

Bethany Reynolds December 7, 2007 at 12:23 pm

Thanks for that tip. I am also a new Numbers user (and not well versed in spreadsheets in general, due to Excel-induced migraines). I have a lot of Excel files which contain ZIP codes, and until Apple adds that as a Special number option, it looks like I will have to convert the zip code coulmns to text and add the 0′s manually….a hideous thought. Is it possible to make a formula to add those 0′s?

Michael December 7, 2007 at 9:45 pm

Bethany,

If you format the zips as text, they will display leading zeros.

Alternatively, you could add leading zeros like so (assuming your zip is in A1): =”000″+A1. You can then wrap this function with the right function, so that it trims the zip down to 5 digits, like so: =RIGHT(“000″+A1,5).

I hope that made sense. If you need more help, or have other questions, you can find some great answers in our forums:

http://www.numberstemplates.com/forums/

- Michael

Yrene October 16, 2008 at 1:11 pm

thanks great!!!!

Christina Coe January 21, 2011 at 12:24 pm

I am trying to format my phone numbers and what you wrote was useful but I need to add a 1+ before the number for calling long distance. How would I do that?

Leave a Comment

Previous post:

Next post: