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.
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
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:
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.· 2 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