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 }

Leave a Comment

Previous post:

Next post: