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:

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
I just noticed the test case example wasn't formatted well and may have caused confusion in my original post.
 

Attachments

  • Screenshot 2024-10-02 214820.jpg
    Screenshot 2024-10-02 214820.jpg
    49.1 KB · Views: 8
Upvote 0
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
ABCDE
1Raw inputFormatted outputDesired outputCustom format
24992949740+49 929 49740+49 929 49740+## ### #####
349928176750+49 928 176750+49 928 176750+## ### ######
4499281738111+49 928 1738111+49 928 1738111+## ### #######
5493012345678+49 30 12345678+49 30 12345678+## ## ########
649928096599104992809659910+49 929 6599210N/AInput does not match desired output
7499296599210+49 929 6599210+49 929 6599210+## ### #######I revised the input to match the desired output
German Phone numbers
 
Upvote 0
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
ABCDE
1Raw inputFormatted outputDesired outputCustom format
24992949740+49 929 49740+49 929 49740+## ### #####
349928176750+49 928 176750+49 928 176750+## ### ######
4499281738111+49 928 1738111+49 928 1738111+## ### #######
5493012345678+49 30 12345678+49 30 12345678+## ## ########
649928096599104992809659910+49 929 6599210N/AInput does not match desired output
7499296599210+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.
 
Upvote 0
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
1Conditional formatting
2+49 929 49740
3+49 928 176750
4+49 928 1738111
5+49 301 2345678
64992809659910
7+49 929 6599210
German Phone numbers
Cell Formulas
RangeFormula
F2:F7F2=A2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F2:F7Expression=LEN(F2)=12textNO
F2:F7Expression=LEN(F2)=10textNO
F2:F7Expression=LEN(F2)=11textNO


cf.jpg
 
Upvote 0
Solution
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
ABCDE
1Raw inputFormatted outputDesired outputCustom format
24992949740+49 929 49740+49 929 49740+## ### #####
349928176750+49 928 176750+49 928 176750+## ### ######
4499281738111+49 928 1738111+49 928 1738111+## ### #######
5493012345678+49 30 12345678+49 30 12345678+## ## ########
649928096599104992809659910+49 929 6599210N/AInput does not match desired output
7499296599210+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.
 
Upvote 0
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.
 
Upvote 0
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:

1727955380935.png


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?
 
Upvote 0
Maybe something like this will work?

Book1
ABCD
1
2Entered number Desired formatLengthCell with CF
34992949740 +49 929 4974010+49 929 49740
449928176750 +49 928 17675011+49 928 176750
5499281738111 +49 928 173811112+49 928 1738111
6493012345678 +49 30 1234567812+49 30 12345678
74992809659910+49 928 0965991013+49 928 09659910
Sheet1
Cell Formulas
RangeFormula
C3:C7C3=LEN(A3)
D3:D7D3=A3
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D3:D30Expression=LEN(D3)=13textNO
D3:D30Expression=AND(LEN(D3)=12; VALUE(MID(D3;3;1))<=6)textNO
D3:D30Expression=AND(LEN(D3)=12; VALUE(MID(D3;3;1))>6)textNO
D3:D30Expression=LEN(D3)=11textNO
D3:D32Expression=LEN(D3)=10textNO
 
Upvote 0

Forum statistics

Threads
1,224,567
Messages
6,179,564
Members
452,926
Latest member
rows and columns

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