Fomula problem

[sandiew9560]

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

 

[Richard Schollar]

Sandie

Formula would be best (avoids the possibility of dropping leading zeros) - this would need to go into an adjacent empty column. Assuming your tel numbers are in column G and first data row is G2 (ie G1 has a heading) then formula in G2 and copied down would be:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(G2, "(", ""), ")", ""), "-", ""), " ", "")&""

Then you can copy and paste this column of formulas over your originals (pastevalues). Yogi's advice to save a copy of your originals is sound though - then you always have something to go back to if something goes wrong.

 

[Yogi Anand]

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

Oh My Goodness Sandie:

You were so clsoe ... but yet so far.

So, now let us take one tiny step at a time.

1) Highlight Column G
2) Click on Edit on the Menu Bar
3) Click on Replace in the subMenu
4) in the DialogBox that pops up ... key-in ( in the Findwhat:
4a) do not enter anything in the Replace with:
4b) click on Replace All
5) Repeat step 4 for deleting )
6) Repeat step 4 for deleting -
7) Repeat step 4 for deleting the space character (you will have to just press the space bar just once in Find what

I hope this helps.

 

[ADAMC]

Edit

**Apologies i didnt even notice you had already responded Yogi **


Hi Sandie....Yogi's suggestion in more detail:

Lets say your data is in column A as per Yogi's post above:
Highlight the whole range of your numbers.
Hold down Ctrl + F
click the replace tab
in find what: (
in replace with, just leave it blank and click replace all, i think your error was you were typing the words 'with nothing' instead of leaving the replace field blank?

Repeat again but this time find ) and click replace all
then repeat again but find - and click replace all
then repeat again but in the find what field press the space bar once and then click replace all, this should do the trick for you!

 

[Yogi Anand]

Hi ADAMC:

Great Job ... between your crystal clear explanation, and that from me, I hope Sandie should get through this with flying colors.

 

[sandiew9560]

YIPPIE....It Worked...OMGsh...Im the happiest person in the world.

YOU GUYS ROCK.
Im sending you so many kisses your faces will hurt.

Sandie

 

[Yogi Anand]

YIPPIE....It Worked...OMGsh...Im the happiest person in the world.
....
Sandie

Great Job Sandie ... Mission Accomplished -- now let us keep EXCELing.