reformatting phone numbers (removing special characters "_,-,/, ,(,),etc..."

I have a DB that uses a text field for phone numbers (very annoying) there is no validation. I want to reformat the entire DB and import it again using a standard format. Currently when I export the numbers I get several different formats. (e.g. (555) 555-5555, 555.555.5555, 555-555-5555, 555 555 5555, and others)

How can I manipulate the exported data in order to remove all characters ("-", "(", ")", etc) and Spaces to get the numbers in this format XXXXXXXXXX, from there I know how to format the phone numbers.

Thank you,

Davesfcm

Hi

Try using find/replace select what you want to find ie - and with the replace put nothing in that should take the dash out and join the two numbers

Chris

Hi

Pleased to help and thanks for the rep.

Chris

Try this Array formula, confirm with Ctrl+Shift+Enter not just Enter

Formula:

Please Login or Register  to view this content.