To post as a guest, your comment is unpublished.· 1 years agoI know it has been 6 months, but here is the answer : The cell is setup to deal with numbers of lenght 10, but a number cannot start with ''0'' (ex: 01 is still 1).
The cell and formulas used should refer to a string instead and this would fix the problem, but would allow people to have letters and you most likely don't want that.
How to apply data validation to force phone number format in Excel?
May be, there are various phone number formats can be used when typing into the Excel workbook. But, how could you only allow one phone number format to be entered in a column of worksheet? For example, I just want the phone number as this format 123-456-7890 to be allowed entering in a worksheet.
Recommended Productivity Tools for Excel
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 71% of your time and solve 82% Excel problems for you. 300+ advanced tools designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
To only allow specific phone number format to be entered, you can solve it with the following steps:
First, you can format the cells to the phone number format as you need.
1. Select the list of cells that you want to only type with the specific phone number format, and then right click, choose Format Cells form the context menu, see screenshot:
2. In the Format Cells dialog box, under the Number tab, click Custom from the left Category pane, and then enter the phone number format as you need into the Type text box, in this example, I will use this ###-###-#### format, see screenshot:
3. And then click OK to close the dialog.
Second, you can apply the Data Validation feature to validate the phone number.
4. After formatting the cells, please click Data > Data Validation > Data Validation, see screenshot:
5. In the Data Validation dialog box, under the Settings tab, select Custom from the Allow drop down, and then enter this formula: =AND(ISNUMBER(A2),LEN(A2)=10) into the Formula text box, see screenshot:
Note: In the above formula, A2 is the first cell of the column that you want to validate the phone number.
6. And then click OK button, now, when you enter a 10 digits number, it will be converted to the specific phone number format automatically as you need, see screenshot:
7. And if the number is shorter or longer than 10 digits, a warning message box will appear to remind you, see screenshot:
Recommended Productivity Tools
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 1 years agoHi. I tried this for my data validation. but how come it didn't work on numbers that starts with zero? eg. 012-345-6789