Phone Number Data Validation

[SJ200]

Hello,

I need to validate a column of phone numbers in excel. The fact that phone numbers in Australia start with zero and also I need the final format to be (eg. (02) 4943-2891), is causing me problems .

I found the following videos on YouTube:

https://www.youtube.com/watch?v=7mrc6cEY8oA

https://www.youtube.com/watch?v=TPZBANarabM

The above videos are on the right track, but I can just enter 123 for example and it spits out (00) 0000-0123 due to the cell formatting. I need it to give an error message if exactly 10 digits are also not entered while still allowing my formatting with brackets, spaces and a dash. The formula in the video I modified that is partially correct is as follows for custom validation:

=IF(J1="",TRUE,IF(ISERROR(SUMPRODUCT(SEARCH(MID(J1,ROW(INDIRECT("1:"&LEN(J1))),1),"0123456789()-"))),FALSE,TRUE))

Is it possible to modify the above formula such that while maintaining my custom formatting it verifies that 10 digits are entered, no more, no less?

I may have stumbled on something excel cannot do. I prefer that the solution does not involve VBA.

Your help would be greatly appreciated.

PS: I am open to any other solutions as well

 

[SJ200]

Hi Rick,

For Australian phone numbers, it does not work as they begin with a zero.

 

[Rick Rothstein]

Hi Rick,

For Australian phone numbers, it does not work as they begin with a zero.

Just wondering... did you actually try it or are you just guessing at how it will work? When I type 0123456789 into a cell custom formatted with (00) 0000-0000 with that Data Validation, it works fine for me (the 0 gets removed automatically because real numbers do not retain leading zeroes)... since the leading digit is always a 0, you do not need to type it as the custom format will put it in for you... and if you do put it in, Excel will remove it automatically.

 

[SJ200]

Hi Rick,

Yes I have tried it, but my problem was never a formatting issue. The formatting always worked fine. My issue was retaining the formatting while still validating that the user entered 10 digits exactly. This combination was the heart of my problem. When I checked the spreadsheet in question I found a few incorrect phone numbers entered. The beauty of using custom data validation is that it counts every entry, even leading zeros, where as in a normal spreadsheet it doesn't. This was a crucial fact that I stumbled onto. Hope this clarifies my issues ...

 

[Rick Rothstein]

My issue was retaining the formatting while still validating that the user entered 10 digits exactly.

Let's make sure we are both talking the same thing here. The only 10-digit numbers that a user should be able to type in are ones where the leading digit is a zero... all other 10-digit numbers should be rejected, correct?

 

[SJ200]

Correct, that is only half the issue. The second part is that I still want to maintain the format (00) 0000-0000.

In Australia the phone area codes are (02),(03),(07) ... and so on, followed by the phone number.

 

[konew1]

I think all of the information required for your stated question has already been given, but I have a question: are you using these numbers for some kind of call center dialing system, or are they to be displayed on screen (or printed) and manually dialed when required?

I expect that an automated dialing system will not see leading zeros, irrespective of whether the zero is entered into the spreadsheet (in which case excel will remove it an then insert a zero to comply with the formatting rules).

 

[jtakw]

Hi,

How about using this formula for data validation, would it satisfy your needs?

If the user input a 10 digit number with 1 leading zero, all fine.
If the user input a 9 digit number with No leading zero, all fine.
Otherwise, the cell won't accept the input.

=AND(LEFT(J1,1)<>0,LEN(J1)+1=10)

 

[SJ200]

I think all of the information required for your stated question has already been given, but I have a question: are you using these numbers for some kind of call center dialing system, or are they to be displayed on screen (or printed) and manually dialed when required?

I expect that an automated dialing system will not see leading zeros, irrespective of whether the zero is entered into the spreadsheet (in which case excel will remove it an then insert a zero to comply with the formatting rules).

No this not for a call centre. This is to ensure correct numbers are inputed

 

[SJ200]

Hi,

How about using this formula for data validation, would it satisfy your needs?

If the user input a 10 digit number with 1 leading zero, all fine.
If the user input a 9 digit number with No leading zero, all fine.
Otherwise, the cell won't accept the input.

=AND(LEFT(J1,1)<>0,LEN(J1)+1=10)

Yes this will work, however the challenge is to maintain the formatting (00) 0000-0000

 

[konew1]

I surrender