Remove Brackets from Telephone Numbers

[ambra19]

I have a column where the telephone numbers were entered with brackets and a dash
ie (902) 555-1234
The column is in Text format.
I need a macro that will strip away the brackets and dashes and leave the number only for the entire column (over 3500 rows).

ie (902) 555-1234 to 9025551234

Any help is greatly appreciated.

Thanks.
ambra19

 

[DonkeyOte]

assuming number in A1 this formula in A2 will return stripped number

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"(",""),")","")," ",""),"-","")

If you want VBA let us know.

 

[ambra19]

Thank you very much. It did the trick!
regards,
ambra19

 

[catharsis50]

I am dealing with the same thing on a regular basis for list uploads and would love a macro to clean these lists.

Thanks!

 

[catharsis50]

I am dealing with the same thing on a regular basis for list uploads and would love a macro to clean these lists.

How would I update this to also eliminate any lead "1","1 ","1-"

Thanks!

 

[catharsis50]

I figured out the rest of the substitute formula.
thx

 

[Scott Huish]

You shouldn't need SUBSTITUTE after that, just take the right 10 characters:

=RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"-",""),")",""),"(","")," ",""),10)

 

[Xx7]

Nice... very logical answer!