Search code examples
excelpython-3.xvbanumber-formatting

formating cell phone numbers

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:

enter image description here

Solution

  • 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.