To post as a guest, your comment is unpublished.· 22 hours agoHello, Rach,
To solve your problem, you just need to use this ## #### #### format in the Format Cells dialog box in step 2. And then follow the above other steps one by one. At last, when you enter a 10 digits number, it will be converted to the phone number format you need automatically.
Please try, hope it can help you!
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.· 1 days agoHi, I know it could be different but how can i validate a country code in front of the phone number for different countries? Eg, ISO code shows 65 for Singapore and phone number format must be 65 6123 4567 or 82 for Korea and phone number format is 82 1234 4567. Without any symbols. Appreciate your help.
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
To post as a guest, your comment is unpublished.· 2 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.