Phone Numbers
 

Hi
I have a column of phone numbers e.g. 1555-555-1212. What formula do I
use to change all the numbers to e.g. 15555551212


--
natei6
------------------------------------------------------------------------
natei6's Profile: http://www.excelforum.com/member.php...fo&userid=7185
View this thread: http://www.excelforum.com/showthread...hreadid=320282

In Cell a1: 1555-555-1212

In Cell b1: =SUBSTITUTE(A1,"-","")

This will remove the dashes


--
crispbd
------------------------------------------------------------------------
crispbd's Profile: http://www.excelforum.com/member.php...o&userid=10880
View this thread: http://www.excelforum.com/showthread...hreadid=320282

If the dashes are part of the data (as opposed to applied by formatting) and
you want them removed permanantly, use Edit/Replace.

On Wed, 24 Nov 2004 17:13:50 -0600, natei6
wrote:


Hi
I have a column of phone numbers e.g. 1555-555-1212. What formula do I
use to change all the numbers to e.g. 15555551212

Do you specifically need a formula?
You could use Edit | Replace "-" (no quotes) and leave the Replace with spot
blank.

If all the phone numbers are in the exact same format-- always four digits,
then a dash, then three digits, then a dash, then four digits, then you could
use this:
=LEFT(B1,4)&MID(B1,6,3)&MID(B1,10,4)
Just copy down. Adjust the cell as necessary.

If the numbers are in a different layout sometimes, this formula won't work.

tj

"natei6" wrote:


Hi
I have a column of phone numbers e.g. 1555-555-1212. What formula do I
use to change all the numbers to e.g. 15555551212


--
natei6
------------------------------------------------------------------------
natei6's Profile: http://www.excelforum.com/member.php...fo&userid=7185
View this thread: http://www.excelforum.com/showthread...hreadid=320282

crispbd Wrote:
In Cell a1: 1555-555-1212

In Cell b1: =SUBSTITUTE(A1,"-","")

This will remove the dashes



Thankyou Very Much, that worked very nicely.


--
natei6
------------------------------------------------------------------------
natei6's Profile: http://www.excelforum.com/member.php...fo&userid=7185
View this thread: http://www.excelforum.com/showthread...hreadid=320282

Thanks Again You have all been very helpful.
tjtjjtjt, I couldn't get the Edit Replace option to work successfully,
it would not allow me to leave the "replace with" spot empty.
Nathan Sargeant



tjtjjtjt Wrote:
Do you specifically need a formula?
You could use Edit | Replace "-" (no quotes) and leave the Replace with
spot
blank.

If all the phone numbers are in the exact same format-- always four
digits,
then a dash, then three digits, then a dash, then four digits, then you
could
use this:
=LEFT(B1,4)&MID(B1,6,3)&MID(B1,10,4)
Just copy down. Adjust the cell as necessary.

If the numbers are in a different layout sometimes, this formula won't
work.

tj

"natei6" wrote:


Hi
I have a column of phone numbers e.g. 1555-555-1212. What formula do
I
use to change all the numbers to e.g. 15555551212


--
natei6

------------------------------------------------------------------------
natei6's Profile:
http://www.excelforum.com/member.php...fo&userid=7185
View this thread:
http://www.excelforum.com/showthread...hreadid=320282




--
natei6
------------------------------------------------------------------------
natei6's Profile: http://www.excelforum.com/member.php...fo&userid=7185
View this thread: http://www.excelforum.com/showthread...hreadid=320282

Hmmm.... everybody else I know can leave it empty.

On Wed, 24 Nov 2004 19:33:31 -0600, natei6
wrote:


Thanks Again You have all been very helpful.
tjtjjtjt, I couldn't get the Edit Replace option to work successfully,
it would not allow me to leave the "replace with" spot empty.
Nathan Sargeant



tjtjjtjt Wrote:
Do you specifically need a formula?
You could use Edit | Replace "-" (no quotes) and leave the Replace with
spot
blank.

If all the phone numbers are in the exact same format-- always four
digits,
then a dash, then three digits, then a dash, then four digits, then you
could
use this:
=LEFT(B1,4)&MID(B1,6,3)&MID(B1,10,4)
Just copy down. Adjust the cell as necessary.

If the numbers are in a different layout sometimes, this formula won't
work.

tj

"natei6" wrote:


Hi
I have a column of phone numbers e.g. 1555-555-1212. What formula do
I
use to change all the numbers to e.g. 15555551212


--
natei6

------------------------------------------------------------------------
natei6's Profile:
http://www.excelforum.com/member.php...fo&userid=7185
View this thread:
http://www.excelforum.com/showthread...hreadid=320282