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]

I just noticed the test case example wasn't formatted well and may have caused confusion in my original post.

 

[6StringJazzer]

The input in your last example doesn't match the formatted version. I can't figure out what you are trying to show there.

You need to set up several conditional formatting rules based on the length of the phone number.

$scratch.xlsm
	A	B	C	D	E
1	Raw input	Formatted output	Desired output	Custom format
2	4992949740	+49 929 49740	+49 929 49740	+## ### #####
3	49928176750	+49 928 176750	+49 928 176750	+## ### ######
4	499281738111	+49 928 1738111	+49 928 1738111	+## ### #######
5	493012345678	+49 30 12345678	+49 30 12345678	+## ## ########
6	4992809659910	4992809659910	+49 929 6599210	N/A	Input does not match desired output
7	499296599210	+49 929 6599210	+49 929 6599210	+## ### #######	I revised the input to match the desired output
German Phone numbers

 

[dowtym]

The input in your last example doesn't match the formatted version. I can't figure out what you are trying to show

You need to set up several conditional formatting rules based on the length of the phone number.

$scratch.xlsm
	A	B	C	D	E
1	Raw input	Formatted output	Desired output	Custom format
2	4992949740	+49 929 49740	+49 929 49740	+## ### #####
3	49928176750	+49 928 176750	+49 928 176750	+## ### ######
4	499281738111	+49 928 1738111	+49 928 1738111	+## ### #######
5	493012345678	+49 30 12345678	+49 30 12345678	+## ## ########
6	4992809659910	4992809659910	+49 929 6599210	N/A	Input does not match desired output
7	499296599210	+49 929 6599210	+49 929 6599210	+## ### #######	I revised the input to match the desired output
German Phone numbers

Jeff, thank you. I am aware of the custom format, but I would prefer not to have to go into each cell and enter a format to match the number of digits. Is that what you are suggesting. I was hoping for a single format that might intelligently do that for me. I know if there are only three possibilities, you can use the Positive; Negative; Zero; Text feature in a custom format, but in this case there are more than three lengths of digits so that strategy fails.

For example, I used this...
[<00000000001]+00 000 000000 ; [>9999999999999]+00 000 0000000 0 ; +00 00000 000000
which covers three scenarios, but not the other two.

 

[6StringJazzer]

I was suggesting using Conditional Formatting, one rule for each format based on the length of the number (5 rules), not individually formatting each cell.

$scratch.xlsm
	F
1	Conditional formatting
2	+49 929 49740
3	+49 928 176750
4	+49 928 1738111
5	+49 301 2345678
6	4992809659910
7	+49 929 6599210
German Phone numbers

Cell Formulas
Range	Formula
F2:F7	F2	=A2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F2:F7	Expression	=LEN(F2)=12	text	NO
F2:F7	Expression	=LEN(F2)=10	text	NO
F2:F7	Expression	=LEN(F2)=11	text	NO

 

[dowtym]

The input in your last example doesn't match the formatted version. I can't figure out what you are trying to show

You need to set up several conditional formatting rules based on the length of the phone number.

$scratch.xlsm
	A	B	C	D	E
1	Raw input	Formatted output	Desired output	Custom format
2	4992949740	+49 929 49740	+49 929 49740	+## ### #####
3	49928176750	+49 928 176750	+49 928 176750	+## ### ######
4	499281738111	+49 928 1738111	+49 928 1738111	+## ### #######
5	493012345678	+49 30 12345678	+49 30 12345678	+## ## ########
6	4992809659910	4992809659910	+49 929 6599210	N/A	Input does not match desired output
7	499296599210	+49 929 6599210	+49 929 6599210	+## ### #######	I revised the input to match the desired output
German Phone numbers

Jeff, thank you. I am aware of the custom format, but I would prefer not to have to go into each cell and enter a format to match the number of digits. Is that what you are suggesting. I was hoping for a single format that might intelligently do that for me. I know if there are only three possibilities, you can use the Positive; Negative; Zero; Text feature in a custom format, but in this case there are more than three lengths of digits so that strategy fails.

 

[dowtym]

Well, there you go! Thanks. You know I've used conditional formatting for decades, but for some reason I had a mental break on this issue. I got hung up on the [positive][negative] rules option in standard formatting.
Solved.

 

[felixstraube]

You will define the different rules for the whole range where you will enter telephone numbers. Depending on the length of the number it will apply one of those rules. No need to apply it manually for each cell.
You will have something like this:

The range it will apply to is C3:C30. The CF's formula says C3, but it is without the $ signs ($C$3). So you can think of this as if you write a formula with C3 as (relative) reference in D3. If you drag the formula in D3 down it will create the formula in D4 with C4 as reference.
Conditional formatting works the same you define the formula for the first cell in the range, but for the cell C4 it will change the formula to "=LEN(C4)=10". And so on.

What i'm not sure is how we would differentiate these two which are of the same length:

499281738111 +49 928 1738111 12
493012345678 +49 30 12345678 12

Do you know what condition have to be met to group the numbers one way or the other? is it the starting number of the area code? Or depending maybe on the first 2 digits of the area code? For example if the area code starts with 7 or above it is a 3 digit area code and if it is 6 and below it is a 2 digits area code. Do you know that or maybe do you have more examples where we can infer this implicit rules?

 

[felixstraube]

Maybe something like this will work?

Book1
	A	B	C	D
1
2	Entered number	Desired format	Length	Cell with CF
3	4992949740	+49 929 49740	10	+49 929 49740
4	49928176750	+49 928 176750	11	+49 928 176750
5	499281738111	+49 928 1738111	12	+49 928 1738111
6	493012345678	+49 30 12345678	12	+49 30 12345678
7	4992809659910	+49 928 09659910	13	+49 928 09659910
Sheet1

Cell Formulas
Range	Formula
C3:C7	C3	=LEN(A3)
D3:D7	D3	=A3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D3:D30	Expression	=LEN(D3)=13	text	NO
D3:D30	Expression	=AND(LEN(D3)=12; VALUE(MID(D3;3;1))<=6)	text	NO
D3:D30	Expression	=AND(LEN(D3)=12; VALUE(MID(D3;3;1))>6)	text	NO
D3:D30	Expression	=LEN(D3)=11	text	NO
D3:D32	Expression	=LEN(D3)=10	text	NO

 

[felixstraube]

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