Suppose you have an Excel file with a list of international phone numbers (like the left column in the image below) and you want to find out which country each number belongs to, group them by country and convert them to a local number for easier dialing.
Or, you have a mix of national and international numbers as in the image below, and you want to convert all of the numbers into the national or international format and make sure they all use consistent formatting.
How might we go about these tasks?
Some background on phone numbers
Let’s start with a bit of background about phone numbers… This is a US phone number:
(212) 555-1234
It consists of the area code and a subscriber number. In this case, the area code is 212 which refers to an area in New York City. The subscriber number is the remaining seven digits. This phone number is in national format. If we dial it from anywhere in the US, the call should go through just fine. However, if we wanted to dial it from abroad, say from Germany, we’d need to include the country code. Since we’re calling into the US, we’d need to prefix the number with the + symbol (for dialing out of the country), and a 1 for North America giving us the following phone number to dial:
+1 (212) 555-1234
Trunk prefixes
US phone numbers are pretty straightforward. But here’s a phone number from a different country:
+441212345678
Is this a national or an international number? It has a plus symbol, so it’s surely in an international format. But which country is it? If we know the country code, we can easily look up the country. But what is the country code for this number? Country codes can have between one and three digits so the country code for this number could be 4, 44 or 441. We could try a Google search for each one of those and we’d find fairly quickly that the calling code was indeed 44, signifying Britain. We could also have checked out the Wikipedia page for country calling codes and found the number quite easily there on the map as well.
Ok, so now we know that +44 is the country code. Could we assume that the rest of the number is a national number that we could dial from inside the UK? Actually no. As in the US, the UK phone number consists of an area code and a subscriber number. However, in the UK, as well as in many other countries, especially in Europe, you have to dial a 0 before dialing the area code. In this example, the area code is 121, indicating Birmingham. So, the number we’d need to call when dialing nationally would be 0 121 2345678.
The 0 we prepended is called a trunk prefix. To translate the national number back into the international format, we’d need to remove the trunk prefix, and add back the + symbol and the country code.
To complicate matters further, different countries can have different rules for the trunk prefix. For example, the trunk prefix in Hungary has two digits: 06.
All of this is to say that translating between national and international numbers are a no easy tasks.
Writing down (formatting) phone numbers
On top of the structural rules described above, there are also different ways of formatting a phone number. A phone number can have up to 15 digits, so to make them more readable, it’s usually a good idea to separate the digits into groups.
For example, in the US, a commonly used format is (xxx) xxx-xxxx where the first group of three digits is the area code and the remaining seven digits are the subscriber number. Another popular way of formatting phone numbers in North America is xxx-xxx-xxxx. In Europe, it’s common to use spaces to separate out groups. Dashes, dots, hyphens, and spaces are all commonly used as separators and much of this comes down to personal preference as well. This means that if you have an Excel file with a list of phone number that various other people have entered, it’s likely the list will have a mix of national and international numbers with all sorts of different formatting styles.
For example, here are several (of many) possible ways to writing down the same US phone number:
+12242937103
+1 (224) 293-7103
224/293-7103
224 2937103
+1-224-2937-103
+1.224.293.7103
Working with a list of numbers that are formatted in such inconsistent ways would be more difficult than necessary.
Instead of relying on users inputting numbers consistently, it would be better if we had the tooling to easily to deal with inconsistently entered data.
Support for phone numbers in Excel
Unfortunately, Excel doesn’t offer much functionality for phone numbers out of the box. There are no built in functions at all for working with phone numbers in Excel.
You can, however, format a number as a US national phone number.
If you need to support any other country, international numbers or different formatting styles, this approach will not work – only national US phone numbers are supported out of the box.
The solution
To make it easier to work with phone numbers in Excel, I’ve made a small extension for Excel called Windy.Phone that adds many useful functions for handling phone numbers. Let me show you what it can do…
Validating phone numbers
So with our list of international numbers, the first question we might want to answer is if the phone numbers look valid. There’s no way to know if the subscriber number is actually assigned, but we can tell if the combination of country code, area code and the length of the phone number are valid. We can do that using the IsValidNumber function.
If the number is a national number, we can supply a default region code.
Extracting country and region code
Next, if we’re starting with international numbers, we might want to know the country and region code which will tell us which part of the world the phone number belongs to.
Converting between national and international numbers
Next, let’s convert all of the phone numbers into the national form so they are easier to dial when calling locally. In the below example, we have a mixed list of national and international numbers, but we do have a region code for each number. We can convert all of these numbers into the international format. We can do that using the ToInternational function.
Alternatively, we can use the Format function to convert the numbers to one of the four standard formats.
Finding phone numbers inside text
To find phone numbers inside text use the FindNumbers function. This function reads through a block of text and returns all of the phone numbers it finds in a specified format. This is a dynamic array function that can return multiple values.
Generating example phone numbers
To get a sample phone number for a given region, use the GetExamplePhoneNumber function. This function returns an example phone number for a given region.
Phone numbers should be stored as text
One last thing to note is that these functions require phone numbers to be stored as text. To prevent Excel from treating the phone number as a numeric value or a formula, either prefix the value with a single quote, or format the cells as text before entering the phone numbers.
Installing these functions into Excel
If you’ve read this far, you might be interested in how you can get these functions into Excel. Here’s how…
Step 1
Go to www.querystorm.com, and then download and install the QueryStorm runtime. Windy.Phone is a package build with a tool called QueryStorm and is distributed through the QueryStorm runtime. The QueryStorm runtime is basically an app store for Excel apps made with QueryStorm.
Step 2
Once the QueryStorm runtime is installed, go to the QueryStorm tab in Excel, click Extensions and in the “Online” tab, find the “Windy.Phone” package and install it. As soon as the package is installed, you can start using these functions in Excel.
Video version of this post
Prefer videos? Here’s a video version of this blog post:
Conclusion
Excel lacks good native support for parsing, validating and formatting phone numbers, but that’s nothing that we can’t fix with a few added functions. If you find yourself working with phone numbers in Excel on a regular basis, give Windy.Phone a try!
If you have any questions or feedback, please feel free to reach out to us at support@querystorm.com
References:
1. List of country calling codes – Wikipedia
3. Random Phone Number Generator (Tool)
4. Libphonenumner-csharp (the library used to implement the Windy.Phone package)