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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Correction...
=TEXT(SUBSTITUTE(H2,"-",""
=TEXT(SUBSTITUTE(H2,"-",""
ASKER
@ssaqibh, this formula does work (had to sort numbers first because of blanks):
=TEXT(SUBSTITUTE(A1,"-",""
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.
=TEXT(SUBSTITUTE(A1,"-",""
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@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.
Glad to help!
Format Cells > Special > Phone Number
Should do the trick...