# Microsoft Office Application Help - Excel Help forum > Excel General >  >  Removing Parenthesis, spaces and dashes from Phone numbers

## rminner

I have a spreadsheet with phone numbers in the format of (111) 222-3333.  I need to convert them all to 1112223333.  Any Ideas?

----------


## contaminated

Can't you re-format them as general?
HAve you tried to set general format to all your number? I think i must work for you.

----------


## Paul

Hi Rminner, and welcome to the forum.  Two quick functions that should work, as long as they're all formatted the same:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"(",""),")","")," ",""),"-","")

or

=MID(A1,2,3)&MID(A1,7,3)&RIGHT(A1,4)

Hope that helps!

----------


## Paul

> Can't you re-format them as general?



If the cells are simply custom formatted to show the symbols, then setting the format back to General would work. However, my guess is that the cells actually contain those characters which need to be removed.  Changing the format to General wouldn't do anything in that case.

----------


## rminner

Thanks the substitute function worked perfectly.

----------


## realest8

> Hi Rminner, and welcome to the forum.  Two quick functions that should work, as long as they're all formatted the same:
> 
> =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"(",""),")","")," ",""),"-","")
> 
> or
> 
> =MID(A1,2,3)&MID(A1,7,3)&RIGHT(A1,4)
> 
> Hope that helps!



Dear Paul, can you please help me i used this formuls it worked but trying to copy and paste values to have it permanent but it only lets me copy the end result not paste the values??

----------


## FDibbins

> Dear Paul, can you please help me i used this formuls it worked but trying to copy and paste values to have it permanent but it only lets me copy the end result not paste the values??



Welcome to the Forum, unfortunately:

_Your post does not comply with Rule 2 of our Forum_ RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

----------


## Aman Kahlon

> I have a spreadsheet with phone numbers in the format of (111) 222-3333.  I need to convert them all to 1112223333.  Any Ideas?



1. First list all numbers in one column. then use text to column under data. (You need to do it separately for all signs i.e - [(, ), -]

2. Then use Formula =CONCATENATE(B1," ",C1," ",D1)

----------

