Phone Numbers

  • Thread starter Thread starter natei6
  • Start date Start date
N

natei6

Hi
I have a column of phone numbers e.g. 1555-555-1212. What formula do
use to change all the numbers to e.g. 1555555121
 
In Cell a1: 1555-555-1212

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

This will remove the dashes
 
If the dashes are part of the data (as opposed to applied by formatting) and
you want them removed permanantly, use Edit/Replace.
 
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
 
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


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

If all the phone numbers are in the exact same format-- always fou
digits,
then a dash, then three digits, then a dash, then four digits, then yo
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'
work.

tj
Click to expand...
 
Back
Top