• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 612
  • 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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

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