Phone Number Region Lookup

Introduction

We call people using phone numbers without giving much thought to what the number represents. Each number actually represents a region of the world and then a specific number in that area. [For some additional details, see

• List of country calling codes - Wikipedia and
• List of North American Numbering Plan area codes - Wikipedia]

The first few digits of a number identify a country, a region, a territory, or a service. This identification uses anywhere from 2 to 7 digits - and I guess it could evolve to fewer or more digits in the future. For example,

• 1-406-xxx-xxxx is region 1-406 (the state of Montana in the U.S.)
• A number can represent a smaller region: 1-416 is a number in the city of Toronto, Canada.
• Or a larger entity. 1-658 is a number in the country of Jamaica.
• However, it does not always take 4 digits to identify a region. A number starting with 20 is in Egypt, 44 is in the UK, and 91 is in India.
• Or, it could be a number starting with the 3 digits. For example, 244 represents Angola.
• So far, we have seen numbers starting with 2, 3, or 4-digit codes. Just to keep things interesting, countries or territories or regions can have numbers starting with 5, 6, or even 7 digits
• 255 24 represents Zanzibar
• A number starting with 39 is in Italy, though
• 39 0549 is in San Marino, and
• 39 06 698 is in Vatican City

While the above may look like a jumble of rules, it turns out there is indeed a method to it all. By working our way from the longest region identifier to the smallest, we can implement this fairly easily in Excel (or in another language, such as SQL).

Using the decodePhoneNumber function in Excel

Download the file decodePhoneNumber.xlsx. Refresh the tables in the worksheets International Phone Codes and NANP Codes.

Then, you can use the function decodePhoneNumber as shown in the worksheet Sample Usage.

More useful would be how to use the function in another workbook as shown here: decodePhoneNumber Sample Use.xlsx

Implement decodePhoneNumber in Excel - Preparation

This section documents the implementation of the decoder.

The workbook with the analysis is available here: decodePhoneNumber.xlsx

For starters, I wanted a source for the codes that I did not have to maintain. Wikipedia provided two pages (listed in the introduction above), one with international codes and the other with area codes for region 1 (North American Numbering Plan).

I used PowerQuery (built into Excel) to access the tables on the web pages and transform them so that they were in a useful format.

Implement the decodePhoneNumber in Excel - the analysis

The key part of the formula to decode the region from a phone number is the formula fragment below. It defines the LAMDA function searchOne and invokes it for all non-Region 1 numbers.

• searchOne looks up the international_codes table with the first _len digits of the phone number. If it fails to find a match, it searches with the first _len-1 digits. If the _len were to become zero it returns the NA() error.
• Before invoking searchOne, the code checks if the region code is 1. If so, it looks up the NANP_codes table.

? ? searchOne,LAMBDA(Nbr,codes,_len,f,
? ? ? ? XLOOKUP(LEFT(Nbr,_len),codes,International_codes[Country, Territory or Service],IF(_len=1,NA(),f(Nbr,codes,_len-1,f)))),
? ? IF(LEFT(Nbr,1)="1",VLOOKUP(MID(Nbr,2,3),NANP_codes,2,FALSE),searchOne(Nbr, codes, _lenToSearch, searchOne))
        

The rest of the formula simply initializes the elements and documents the formula.

=LET(_ref,"https://en.wikipedia.org/wiki/List_of_country_calling_codes",
? ? _ref_NANP_codes,"https://en.wikipedia.org/wiki/List_of_North_American_Numbering_Plan_area_codes",
? ? Nbr,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D2," ",""),"+",""),"-",""),
? ? codes,MID(SUBSTITUTE(International_codes[Code]," ",""),2,255),
? ? _lenToSearch,MAX(LEN(codes)),
? ? searchOne,LAMBDA(Nbr,codes,_len,f,
? ? ? ? XLOOKUP(LEFT(Nbr,_len),codes,International_codes[Country, Territory or Service],IF(_len=1,NA(),f(Nbr,codes,_len-1,f)))),
? ? IF(LEFT(Nbr,1)="1",VLOOKUP(MID(Nbr,2,3),NANP_codes,2,FALSE),searchOne(Nbr, codes, _lenToSearch, searchOne)))        

The evolution of the above is the named function decodePhoneNumber. It is array-aware and so one can use it as

=decodePhoneNumber(B2:B13)        

Summary

While phone numbers have a variable number of leading digits to designate a country, territory, or service, there is a logic to mapping a number to a region. This article with its associated workbooks illustrates how.

My previous article: https://www.dhirubhai.net/pulse/ms-excel-graph-spill-range-tushar-mehta