Jump to content

Phone Numbers in Excel


Recommended Posts

Phone Numbers in Excel

I'm trying to move a column of numbers that were derived by a formula into another column in phone number format. I started with 13 text digits in column A. In column B, I used the formula =LEFT(A2,10) to get the digits in 10-digit format. Now I need to get the numbers in telephone number format ((###) ###-####) in column C. Is there an easy way to accomplish this?

There are two easy ways, and you can quickly try them both. In C2, enter the formula =VALUE(B2) and copy it down the length of column B. Now highlight all of those cells, right-click, and select Format Cells. Choose Special from the Category list on the left-hand side of the window and choose Phone Number from the Type list on the right-hand side. You now have the data stored as 10-digit numbers but displayed as phone numbers.

You can also build a text string with the desired format. In cell D2, enter this formula: ="(" & LEFT(B2,3) & ") " & MID(B2,4,3) & "-" & RIGHT(B2,4). Copy the formula to all of column B. Columns C and D now look the same, but C holds formatted numbers and D contains the data in text form. Use whichever best suits your needs.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Create New...