• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 607
  • Last Modified:

Excel 2010 format cell

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.
0
fjkaykr11
Asked:
fjkaykr11
  • 3
  • 2
  • 2
  • +2
3 Solutions
 
Saqib Husain, SyedEngineerCommented:
You can try a formula like

=TEXT(SUBSTITUTE(A1,"-",""),"(000)000-000")
0
 
McOzCommented:
Have you tried using

Format Cells > Special > Phone Number

Should do the trick...
0
 
Saqib Husain, SyedEngineerCommented:
Correction...

=TEXT(SUBSTITUTE(H2,"-",""),"(000)000-0000")
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
fjkaykr11Author Commented:
@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.
0
 
aindelicatoCommented:
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.
0
 
Saqib Husain, SyedEngineerCommented:
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
Ctrl-C
select the range again
paste-special with option to "Add"
Now apply your desired option
0
 
Patrick MatthewsCommented:
Expanding upon ssaqibh's and aindelicato's suggestions...

=TEXT(TRIM(SUBSTITUTE(SUBSTITUTE(CLEAN(A1),"-",""),CHAR(160),"")),"(000)000-000")

That takes care of:
Leading/trailing spaces
Non-breaking space
Dashes
Various non-printing characters such as line feed, carriage return, and tab
0
 
fjkaykr11Author Commented:
@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.
0
 
aindelicatoCommented:
Glad to help!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now