Phone Number Region Lookup

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

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.

No alt text provided for this image
Partial List of International Codes


No alt text provided for this image
Partial List of North America Number Plan Area Codes

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

Yitzie Steinberger

Financial Planning & Analysis – I partner with Commercial, Operations and Project teams to drive performance and profit – Excel whizz – I am collaborative, strategic and commercially focussed. NW Manchester

2 年

Great article, and advanced method. Crazy coincidence that I posted my Country codes challenge literally 2 days before!

回复

要查看或添加评论,请登录

Tushar Mehta的更多文章

  • MS Excel: How to develop a recursive function

    MS Excel: How to develop a recursive function

    Introduction A recursive function returns a value that depends on the value that the function returns for another set…

    2 条评论
  • MS Excel - Graph a Spill Range

    MS Excel - Graph a Spill Range

    Introduction to Dynamic Array Formulas and Spill Ranges Microsoft implemented a major improvement in Excel with the…

  • List and count unique values (MS Excel, Python, and SQL)

    List and count unique values (MS Excel, Python, and SQL)

    This is a fairly common requirement that until recently had a convoluted solution in Excel. Given a list of values, how…

    1 条评论
  • Excel Large Number Arithmetic with Python

    Excel Large Number Arithmetic with Python

    Introduction When it comes to numbers, MS Excel follows the IEEE 754 standard (Floating-point arithmetic may give…

  • MS Excel: Recursive functions

    MS Excel: Recursive functions

    In MS Excel: Functions are first-class objects, we saw how a custom Excel function, using LAMBDA, can call another…

    3 条评论
  • MS Excel: Functions are first-class objects

    MS Excel: Functions are first-class objects

    What makes a function a first-class object? This is almost certainly an advanced discussion. And, we will start with a…

  • Generate unique random integers

    Generate unique random integers

    Generating unique random integers is an important task in various domains. Examples include data sampling, simulations,…

    2 条评论
  • MS Excel: Goal Seek or Algebra

    MS Excel: Goal Seek or Algebra

    Let's start with a question. I want to pay a business through a payment processor such as PayPal.

    1 条评论
  • Calendar for the New Year

    Calendar for the New Year

    For the New Year..

    1 条评论
  • Validating U.S. Bank Routing Numbers

    Validating U.S. Bank Routing Numbers

    In the U.S.

社区洞察

其他会员也浏览了