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

 

[jtakw]

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

You can still use that formatting. That's the whole idea. Just try it.

 

[SJ200]

I surrender

All good, at least I have a solution, even though somewhat complex ...

Thank you for your help.