Tuesday, February 23, 2010

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!

3 comments:

Robert McMahon said...

Thanks for that very helpful. I made a couple of minor changes
- Australian country code
- Removal of leading zeros (Australian phone numbers have leading zeros when dialling locally)

=IF(D2="","",CONCATENATE("+61",IF(LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D2,"(",""),")","")," ",""),"-",""),".",""),"x",";ext="),1)="0",RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D2,"(",""),")","")," ",""),"-",""),".",""),"x",";ext="),LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D2,"(",""),")","")," ",""),"-",""),".",""),"x",";ext="))-1),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D2,"(",""),")","")," ",""),"-",""),".",""),"x",";ext="))))

Tabitha said...

This was incredibly helpful! Thank you so much!

Tabitha said...

This was incredibly helpful! Thank you!