Phone Number Data Validation

SJ200

New Member
Joined
Jun 11, 2016
Messages
14
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 :)
 
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.
 
Last edited:
Upvote 0

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Forum statistics

Threads
1,213,494
Messages
6,113,981
Members
448,538
Latest member
alex78

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