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

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
Joined
Jun 23, 2006
Messages
7,128
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
  2. Mobile
  3. Web
Does this work for you

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

KR


Dave
 

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,128
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
  2. Mobile
  3. Web
Forgot to say

Welcome to the Board
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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, "(", ""), ")", ""), "-", ""), " ", "")&""
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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?
 

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,128
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
  2. Mobile
  3. Web
Good point, Thanks Richard
 

sandiew9560

New Member
Joined
Jul 29, 2007
Messages
5
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
Joined
Mar 12, 2002
Messages
11,454
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
Joined
Jul 29, 2007
Messages
5
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
Joined
Jul 29, 2007
Messages
5
Ok it didn't take off the ( )
when I did general. didn't scroll far enough to see the numbers that did have the ( )
 

Forum statistics

Threads
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.
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
Top