# Fomula problem

#### sandiew9560

##### New Member
I have a problem that I need to find a formula for. I have a very large spread sheet that has 16000 client names phone # and all info we need.
We are in the process of working with the new VOIP phones. The company handling all the phone programing needs our phone numbers with out the ( ) or -'s I have tried everything to take out that information from each cell with out having to do each one individually. It will take me weeks to take all those out. Is there a formula that will take a number like (888) 321-1234 and make it 8883211234

Sandie

### Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

#### dave3009

##### Well-known Member
Does this work for you

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D17, "(", ""), ")", ""), "-", ""), " ", "")

KR

Dave

#### dave3009

##### Well-known Member
Forgot to say

Welcome to the Board

#### Richard Schollar

##### MrExcel MVP
Hi Sandie

If you want to ensure you don't drop leading zeros (ie if you have a number like (001) 321-1234 then you need to modify Dave's formula slightly:

Code:
``=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D17, "(", ""), ")", ""), "-", ""), " ", "")&""``

#### Yogi Anand

##### MrExcel MVP
I have a problem that I need to find a formula for. I have a very large spread sheet that has 16000 client names phone # and all info we need.
We are in the process of working with the new VOIP phones. The company handling all the phone programing needs our phone numbers with out the ( ) or -'s I have tried everything to take out that information from each cell with out having to do each one individually. It will take me weeks to take all those out. Is there a formula that will take a number like (888) 321-1234 and make it 8883211234

Sandie
EDIT: removed e-mail address caught in Yogi's quote - Smitty

Hi Sandie:

Welcome to MrExcel Board!

Let us have alook at the following ...

Excel Workbook
ABC
1BeforeEntriesAfterEntries
2(888) 321-12348883211234
3(888) 321-1235How about using EDIT|Replace 8883211235
4(888) 321-12361) replace ( with nothing8883211236
5(888) 321-12372) replace ) with nothing8883211237
6(888) 321-12383) replace - with nothing8883211238
7(888) 321-12394) replace space character with nothing8883211239
8(888) 321-12408883211240
9(888) 321-12418883211241
10(888) 321-12428883211242
Sheet3

</body></html>

Would this do?

#### dave3009

##### Well-known Member
Good point, Thanks Richard

#### sandiew9560

##### New Member
it tells me Microsoft excel cant find matching data. I am a dummy at excel. I have been working on it for over a year but mostly data entry. Can you give me step by step.
I like the replace but I think I am missing something. I also tried the formula but it won't let me add that fomula.
I work by piece. They told me I won't get paid for this info. Even though no one told me I was not suppose to enter the data that way.
I am being a big baby girl. I want to cry...LOL
Sandie

#### Yogi Anand

##### MrExcel MVP
it tells me Microsoft excel cant find matching data. I am a dummy at excel. I have been working on it for over a year but mostly data entry. Can you give me step by step.
I like the replace but I think I am missing something. I also tried the formula but it won't let me add that fomula.
I work by piece. They told me I won't get paid for this info. Even though no one told me I was not suppose to enter the data that way.
I am being a big baby girl. I want to cry...LOL
Sandie

Hi Sandie:

How did you try using EDIT|Replace?

What you may want to do is highlight the range which houses the PhoneNumbers to be fixed

and then follow the steps in my previous post.

Please save a the file with your original data first (in case something goes wrong).

Then do the EDIT|replace on a copy of the data.

Just 4 REPLACEs and you are done.

Let us know how it goes and do post back if you need to discuss it further.

#### sandiew9560

##### New Member
I was able to get rid of the ( ). Just by using general.
What I did was go to
Highlight the column G go to
Edit
in the replace area I put
- with nothing
It gave me that message I stated above
Do I need to put something in the
Find What box

I put search by columns and dont check either box
one box Match Cast
Find entire cell only

#### sandiew9560

##### New Member
Ok it didn't take off the ( )
when I did general. didn't scroll far enough to see the numbers that did have the ( )

Replies
0
Views
225
Replies
3
Views
131
Replies
4
Views
182
Replies
5
Views
199
Replies
5
Views
209

1,181,418
Messages
5,929,796
Members
436,695
Latest member
yashpisat

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back