Need to format telephone numbers into 10 digit number only.

[tamwink]

How do I remove the country codes, hyphens, and "+1" from a column of data?
All information appreciated.

 

[Fluff]

Hi & welcome to MrExcel
It would help if you could some representative data.
Follow the XL2BB link in the quick reply window & you can use that to put copyable data in your post.

 

[tamwink]

17206292779, 1-678-416-8080, 1(334) 662-9740, p:+117579209003 and I need them all to be 10 digit phone numbers.

 

[Fluff]

How about

Book1
	A	B
1	Old	New
2	17206292779,	7206292779
3	1-678-416-8080,	6784168080
4	1(334) 662-9740,	3346629740
5	p:+117579209003	7579209003
Mon

Cell Formulas
Range	Formula
B2:B5	B2	=RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,",",""),"-",""),"(",""),")","")," ",""),10)

 

[tamwink]

My apology but I can't seem to make the XL2BB work for me.
I tried using that formula in the cells but nothing changed in my data.
Am I doing something else incorrectly?
I really do know how to operate in Excel.

 

[Fluff]

Is your data laid as shown in post#4?

 

[tamwink]

Yes. The spreadsheet has 40,000 lines, if that matters.

 

[Fluff]

What does the formula return?

 

[tamwink]

When I insert the formula (=RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,",",""),"-",""),"(",""),")","")," ",""),10)), it changes a few cells of numbers but not all of them. Still missing about 30,000 lines that need to be changed. Am I doing something incorrectly?

 

[tamwink]

Also, due to running this formula, now I can't manually edit the spreadsheet. Does this seem right to you?