Removing Parenthesis, spaces and dashes from Phone numbers

**rminner** · 01-14-2010, 01:37 PM

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** · 01-14-2010, 01:48 PM

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** · 01-14-2010, 01:49 PM

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** · 01-14-2010, 01:50 PM

Originally Posted by contaminated

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** · 01-14-2010, 03:28 PM

Thanks the substitute function worked perfectly.

**realest8** · 11-15-2012, 03:50 PM

Originally Posted by 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!

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** · 11-15-2012, 04:21 PM

Originally Posted by realest8

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** · 03-03-2016, 11:38 AM

Originally Posted by 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?

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)