Changing phone number format in Numbers '08

Question

Changing phone number format in Numbers '08

Hi,
How do I change a column of phone numbers from 5035551212 to (503) 555-1212?

If I can't get the parenthesis, then at least to make it easier to read a phone number (e.g. spaces, dash).

Thanks,
Mark

macbook, Mac OS X (10.4.10)

Posted on Nov 5, 2007 2:31 PM

Reply

Answer 1

WWJD

Level 6

15,830 points

Dec 21, 2010 2:07 PM in response to marklovesportland

You can format the cell as text and enter the parenthesis & spaces manually.

Regards,

Reply

Answer 2

Dec 21, 2010 2:07 PM in response to WWJD

You're kidding, right?

I am speaking of hundreds of numbers, in a column, that I want to "change" to an easier-to-read phone number format.

Reply

Answer 3

5-vv

Level 3

685 points

Nov 6, 2007 4:39 AM in response to marklovesportland

If your phone numbers are in column A, for instance, you can create a new column populated (filled down) with a formula like:

="("&LEFT(A1,3)&") "&MID(A1,3,3)&"-"&RIGHT(A1,4)

If you'd then rather not retain the old column A values, then select this new column and "Edit"/"Cut" and then select column A and "Edit"/"Paste Values".

Reply

Answer 4

Dec 21, 2010 2:07 PM in response to 5-vv

Hi,
Thanks for the formula. I tried it, but noticed that my phone number "changes" one digit.

="("&LEFT(A1,3)&") "&MID(A1,3,3)&"-"&RIGHT(A1,4)

for example, when I use (503) 555-1212, it changes to
(503) 355-1212.

I'm too new to Numbers to understand the formula, but this is definitely on the right track.

Mark

Reply

Answer 5

WWJD

Level 6

15,830 points

Dec 21, 2010 2:07 PM in response to marklovesportland

Value to be parsed is in cell A3.

Use:

="("&LEFT(A3,3)&") "&MID(A3,4,3)&"-"&RIGHT(A3,4)

Regards,

Look at Help for the MID function to get better understanding what the function does.

Reply

Answer 6

5-vv

Level 3

685 points

Dec 21, 2010 2:07 PM in response to WWJD

Sorry... there was a silly error in my formula that caused it to double up third digit. WWJD's formula has this corrected. I'm glad you caught it.

Reply

Answer 7

Dec 21, 2010 2:08 PM in response to 5-vv

Hi,
Thank you both for your help, as this is PERFECT for me!

Mark

Reply

Answer 8

WWJD

Level 6

15,830 points

Dec 21, 2010 2:15 PM in response to marklovesportland

Please send Feedback to Apple for expanded formatting for values (Telephone, Zip Code, etc...) You can find this on Numbers' main menu.

Regards,

Reply

Answer 9

Dec 21, 2010 2:16 PM in response to 5-vv

5|-vv,

Nice tip. I wrote up a step by step, walk through for it on the blog. You can find it here:

http://www.numberstemplates.com/2007/11/07/tip-formula-to-format-telephone-numbe rs/

Thanks again,

Michael

Reply