How to unformat telephone numbers

[JayBoston]

First spreadsheet: I have a spreadsheet with thousands of phone numbers on it. The phone numbers are formatted with dashes: 555-555-1212 I need to remove the dashes so the phone numbers look like this: 5555551212

Second spreadsheet: I have a second spreadsheet with phone numbers formatted with a "1" in front of all the phone numbers: 15555551212 i want to remove the 1's to make the phone numbers look like this: 5555551212

How? I'm a newbie, so please dumb down your answer. I just need the cell code. Thanks.

 

[Michael M]

for the first one simply use Find /Replace
Find -
Replace with leave the dialog box blank
for the 2nd one, try this and copy down as far as required

Excel 2007
	A	B
1	15555551212	5555551212
Sheet1

Cell Formulas
Range	Formula
B1	=MID(A1,2,99)

 

[Momentman]

Here you have 2 formulas for the 2 cases

Excel 2010
	A	B
1	555-555-1212	5555551212
2
3	15555551212	5555551212
Sheet2

Cell Formulas
Range	Formula
B1	=SUBSTITUTE(A1,"-","")
B3	=RIGHT(A3,LEN(A3)-1)

 

[shg]

Duplicate post at http://www.mrexcel.com/forum/excel-questions/809686-how-un-format-phone-numbers.html#post3957384

 

[Michael M]

Darn....I hate that...