Deleting characters that are not numbers

[jermsalerms]

I have a column of 4000 phone numbers that are entered in severa
different ways. Such as.

(222) 222-2222
(222)222-2222
222-222-2222
222 222 2222
2222222222

I have set up a helper sheet for converting data and need to make sur
they all all converted to a format of:

2222222222

How do I write a formula to remove the colons, spaces, and dashe

 

[Dave Peterson]

Record a macro when you select the column
edit|replace ( with nothing
) with nothing
- with nothing
space with nothing

And maybe continue recording when you change the number format.

then stop recording.

 

[Guest]

This will work:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"(",""),")",""),"
",""),"-","")

 

[jermsalerms]

I went with the substitution method since I am not familiar with macros
yet

 

[Dave Peterson]

The formula seems reasonable if you have to do it lots of times.

But if you only have to fix the data once, do a bunch of edit|replaces seems
like it might be quicker.