Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Seed numbers for random number generation, uniform distribution | Excel Discussion (Misc queries) | |||
Phone Dialer | Excel Discussion (Misc queries) | |||
Sorting when some numbers have a text suffix | Excel Discussion (Misc queries) | |||
Sorting imported "numbers" | Excel Discussion (Misc queries) | |||
finding common numbers in large lists | Excel Worksheet Functions |