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

 

[FDibbins]

Apart from your custom cell formatting, you could use Data Validation with this rule...
=LEN(cell-ref)=10

This will then not allow you to enter anything in that cell that is not 10 characters
=LEN(B10)=10

If you add this, it will then on.ly allow numeric entries, and not allow any text
=AND(ISNUMBER(B10),LEN(B10)=10)

 

[SJ200]

Thank you for your prompt reply. Seems my custom formatting is the problem, yet I still need the end result to be in the above format. Any thoughts?

 

[Rick Rothstein]

Seems my custom formatting is the problem, yet I still need the end result to be in the above format. Any thoughts?

Does this Custom Format work for you...

(00) 0000-0000

 

[konew1]

I don't see that the custom format is the problem.
A different approach in data validation is to require a whole number between 100000000 and 999999999 (to make easier to read 100,000,000 and 999,999,999.

 

[SJ200]

Hello Good People,

Great to hear from all of you. Firstly, yes, (00) 0000-0000 is the formatting I am currently using. I have been inspired by all you and I think I found the solution.

Consider this formula in the custom data validation:

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

Few things I have added to my original formula :

1. Added an AND function and included LEN(phone) to limit the number of characters to 10
2. This now allows numbers, leading zeros, ( ) & - in the formatting.
2. I replaced the ref. cell with a dynamic named range called "phone" so I can apply the validation to the column.

This seems to work. Please let me know if there is a simpler solution.

 

[Rick Rothstein]

I don't see that the custom format is the problem.
A different approach in data validation is to require a whole number between 100000000 and 999999999 (to make easier to read 100,000,000 and 999,999,999.

Yes, that looks like a good approach.

 

[konew1]

When I make a custom format with ( and ) and - and then enter a 9 digit number 123456789 it displays as (01)2345-6789 and in the next cell I put =Len(A1) it will return 9. The leading zero, the ( and ) and the - are formatting only, they are not part of the data, and are not counted in the LEN function.
Your latest formula is too complicated for me to understand in less than 10 minutes and I suggest you look at the simple suggestions already posted

 

[SJ200]

Hi konew1,

Yes, the formula is long and complex, I found the main part on YouTube (refer post #1 for links). Unless explained, its logic would not be apparent to anyone, however this modified formula on post #6 is the only thing that works for me. Any other suggestions would be most welcome.

 

[Rick Rothstein]

Any other suggestions would be most welcome.

See Message #5.. that is the way to do it.