Normalizing Phone Numbers to E.164 format in Excel

Recently, I had the need to import some users for a large company. In order to populate as much of their Active Directory as possible, they wanted their phone numbers to be in a standardized format. Both Microsoft and Cisco have standardized on E.164 (additional information here) as a numbering standard, which basically starts with + [country code] + phone number.

This particular customer is US based only, so all the numbers in their spreadsheet had a US country code of 1. If I had a multinational organization, some additional coding would need to be done to account for other country codes.

My major need was to simply re-input all the different numbering standards the various internal organizations had inputted their phone numbers as. In other words, normalization. This helps to set up AD for later integration of OCS, or other VoIP systems, as well as Exchange 2007 or Exchange 2010 UM.

Either way, the Excel formula I was using here was the following:
=CONCATENATE("+1",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE((SUBSTITUTE(A2,"(","")),")","")," ",""),"-",""),".",""),"x",";ext="))

In logical order..

1. Replace ( with null
   
2. Replace ) with null
   
3. Replace space with null
   
4. Replace hyphen with null
   
5. Replace period with null
   
6. Replace x with ";ext=" (which is the E164 standard for non-DID numbers)
   
7. Concatenate the +1 country code
   

Here again without the horrible color:
=CONCATENATE("+1",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE((SUBSTITUTE(A2,"(","")),")","")," ",""),"-",""),".",""),"x",";ext="))

The end result, computer readable phone numbers!