Telephone numbers are Hard!

[dowtym]

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.

 

[dowtym]

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.