Deleting characters that are not numbers

[jermsalerms]

I have a column of 4000 phone numbers that are entered in several 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 sure they all all converted to a format of:

2222222222

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

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

jermsalerms wrote:
>
> I have a column of 4000 phone numbers that are entered in several
> 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 sure
> they all all converted to a format of:
>
> 2222222222
>
> How do I write a formula to remove the colons, spaces, and dashes
>
> --
> jermsalerms
> ------------------------------------------------------------------------
> jermsalerms's Profile: http://www.excelforum.com/member.php...o&userid=30167
> View this thread: http://www.excelforum.com/showthread...hreadid=500784

--

Dave Peterson

[David Billigmeier]

This will work:

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

--
Regards,
Dave


"jermsalerms" wrote:

>
> I have a column of 4000 phone numbers that are entered in several
> 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 sure
> they all all converted to a format of:
>
> 2222222222
>
> How do I write a formula to remove the colons, spaces, and dashes
>
>
> --
> jermsalerms
> ------------------------------------------------------------------------
> jermsalerms's Profile: http://www.excelforum.com/member.php...o&userid=30167
> View this thread: http://www.excelforum.com/showthread...hreadid=500784
>
>

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



jermsalerms wrote:
>
> I went with the substitution method since I am not familiar with macros
> yet
>
> --
> jermsalerms
> ------------------------------------------------------------------------
> jermsalerms's Profile: http://www.excelforum.com/member.php...o&userid=30167
> View this thread: http://www.excelforum.com/showthread...hreadid=500784

--

Dave Peterson