Deleting characters that are not numbers

J

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
 
D

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.
 
D

David Billigmeier

This will work:

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

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