Excel 2010 format cell

Posted on 2012-08-16
Last Modified: 2012-08-16
I have a spreadsheet with a list of phone numbers displayed with a text format:
example: 555-760-1234
I need to have it displayed as (555) 760-1234.
I tried to format > special > phone number and also format > custom > (###) ###-####
but nothing happens when I try to change the format.  Any ideas?
I know I can add columns with (  ) and - and then combine the cells but I am looking for a  faster method since I have to repeat this process often.  I am not sure why the format option doesn't work in this case. It works when I format the date, time, etc.
Thanks for any help.
Question by:fjkaykr11
    LVL 43

    Assisted Solution

    by:Saqib Husain, Syed
    You can try a formula like

    LVL 9

    Expert Comment

    Have you tried using

    Format Cells > Special > Phone Number

    Should do the trick...
    LVL 43

    Expert Comment

    by:Saqib Husain, Syed

    LVL 3

    Author Comment

    @ssaqibh, this formula does work (had to sort numbers first because of blanks):
    =TEXT(SUBSTITUTE(A1,"-",""),"(000) 000-0000")
    However. I want to see if any else posts as to why the format > special > phone number doesn't seem to work for me. I must be doing something wrong.
    LVL 12

    Accepted Solution

    Did you copy the data from somewhere else?  There may be hidden characters (like leading spaces) that are throwing off the formatting.

    Try scrubbing the data in notepad first.
    LVL 43

    Expert Comment

    by:Saqib Husain, Syed
    To make that work try the following steps

    Select the cells range
    do a find-replace and replace all hyphens "-" with blanks  ""
    select a blank cell
    select the range again
    paste-special with option to "Add"
    Now apply your desired option
    LVL 92

    Assisted Solution

    by:Patrick Matthews
    Expanding upon ssaqibh's and aindelicato's suggestions...


    That takes care of:
    Leading/trailing spaces
    Non-breaking space
    Various non-printing characters such as line feed, carriage return, and tab
    LVL 3

    Author Closing Comment

    @aindelicato that worked.  I tried to copy as text to another spreadsheet but that didn't work. I had to copy it from notepad back into excel as a General format and then choose Format > Special > Phone Number.  Thanks so much.  Thanks to @ ssaqibh and @mathewspatrick as well.
    LVL 12

    Expert Comment

    Glad to help!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now