formating cell phone numbers

Question

I'm working on data cleaning on a Google sheet, there're 10,000 rows containing the US cell phone numbers in different formats.

For example,

• (888)888-8888
• 888-888-8888
• +1 888-888-8888

or

• empty

I hope to convert them all to the format below with no country code, etc.

• (888) 888-8888

or

• empty

Due to the large amout of data, I'm not sure Google sheet has similar function to convert them all, I'm thinking to download them to an excel file：

• use excel vba
• use Python function to convert them to the right format and write them back to the excel file

I need a few ideas on this topic brothers.

Sample:

Answer 1

This site provides state area codes as well as district codes via html links. You could go through the painstaking process of defining a matrix of states/districts to area codes with excel or a text document. This matrix should also include any abbreviations or contractions of the state/district name.

Or you could try using a web crawler API to automate the process for you, even looking for a dataset that already contains the above mentioned information would go a long way.

Then write a function to evaluate the address against the matrix and then edit the number so that the area code is prefixed to the number.