Deleting characters that are not numbers
 

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

Deleting characters that are not numbers
 

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

Deleting characters that are not numbers
 

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

Deleting characters that are not numbers
 

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

Deleting characters that are not numbers
 

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