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,
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,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
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!