Telephone numbers are Hard!

dowtym

New Member
Joined
Oct 11, 2005
Messages
17
Office Version
  1. 365
Platform
  1. Windows
I'm creating a spreadsheet that contains a large variety of international phone numbers. Germany is tricky insofaras the number of digits is not a constant, and unfortunately it has more than three variant. That last fact is preventing me from formatting telephone numbers properly. Ideally I want to past in an unformatted number, and allow excel to format it. Knowing that I'm not the first person to enter German phone numbers into excel, I'm wondering if there is a clever solution to this problem.
The format is Country Code (a constant for germany), an area code (from 2-5 digits in you drop the trunk 0 as I will), a local number (from 3 to 8 and rarely 9 digits). Also, in total, there are 10 to 13 (and rarely 14) digits (including the +49 (three "digits") but without the trunk 0). FYI: the trunk 0 is the leading zero that is dropped when dialing internationally and is placed before the area code. I will be importing numbers that already have the trunk 0 deleted.

My input (for testing) might be....
Input Formatted Number of Digits
4992949740 +49 929 49740 10
49928176750 +49 928 176750 11
499281738111 +49 928 1738111 12
493012345678 +49 30 12345678 12
4992809659910 +49 929 6599210 13



Note that the third and the fourth numbers in that list have the same number of digits but different desired formats. I don't think this could be solved intelligently unless there is knowledge of what the allowable area codes are. I could import such a list and write some conditionals around it, but honestly I'm not concerned about that at the moment. If both resulted in the same format (with a three digit area code) I'd be ok with that for now.
 
Last edited by a moderator:
As i can see from this wikipedia page it is a little more complex:

List of dialling codes in Germany - Wikipedia

to consider all cases you would need to have a helper table where you have all the different formats associated with each code range.

here is the same (more complete) list:

https://www.itu.int/dms_pub/itu-t/oth/02/02/T02020000510006PDFE.pdf
Oh, yes, it's massively complex for German numbers. It doesn't help that there are actually three different standards that are competing. But for my purposes, I think the conditional formatting will likely be sufficient. Thanks Felix.
 
Upvote 0

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Forum statistics

Threads
1,224,568
Messages
6,179,586
Members
452,927
Latest member
whitfieldcraig

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top