Fomula problem

sandiew9560

New Member
Joined
Jul 29, 2007
Messages
5
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

Please please I need help
Sandie
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Does this work for you

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

KR


Dave
 
Upvote 0
Forgot to say

Welcome to the Board
 
Upvote 0
Hi Sandie

You should remove your email address from your message unless you want to be the target of spammers.

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, "(", ""), ")", ""), "-", ""), " ", "")&""
 
Upvote 0
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

Please please I need help
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 ...

<html><head><title>Excel Jeanie HTML</title></head><body>
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Ok it didn't take off the ( )
when I did general. didn't scroll far enough to see the numbers that did have the ( )
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top