Скачать или смотреть Limit Input to Digits and Dashes in Excel Data Validation

Learn how to enforce data validation for phone numbers in Excel, allowing only `digits` and `dashes` while ensuring at least one dash is present.
---
This video is based on the question https://stackoverflow.com/q/36153401/ asked by the user 'frenzy' ( https://stackoverflow.com/u/4474576/ ) and on the answer https://stackoverflow.com/a/62293986/ provided by the user 'ChrisB' ( https://stackoverflow.com/u/5640342/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Excel data validation - how to limit input to digits and dashes?

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 3.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Limiting Input to Digits and Dashes in Excel Data Validation

When entering phone numbers into Excel, it’s essential that the information is formatted correctly. If your data should consist of only digits and dashes, with at least one dash necessary, you might find yourself asking: How can I achieve this through data validation without resorting to VBA? Don’t worry; this guide will guide you step by step through the process you need!



Understanding Data Validation in Excel

Data validation is a feature in Excel that allows you to control what data can be entered into a cell. By using this feature, you can create rules that ensure users input data in a specified format. In our case, we want to ensure that only phone numbers containing digits and dashes are accepted.



How to Set Up Data Validation for Phone Numbers

To implement data validation that limits input solely to digits and dashes, follow these steps:

Access Data Validation Settings

Navigate to the Data tab.

Click on the Data Validation option from the ribbon.

Configure Validation Criteria

In the Data Validation dialog box, set the Allow drop-down menu to "Custom".

In the Formula field, input the following formula, assuming the first cell in your range is A1:

[[See Video to Reveal this Text or Code Snippet]]

Breakdown of the Formula

SUBSTITUTE Function: The SUBSTITUTE function replaces all dashes (-) in the value of cell A1 with nothing, effectively removing them.

Multiplication by 1: The result (after removing dashes) is then multiplied by 1. This step converts the value to a number, provided there are only digits present.

ISNUMBER Function: Finally, the ISNUMBER function checks if the result of the multiplication is indeed a number. If there were any non-digit characters (other than dashes), the multiplication would return an error, and ISNUMBER would return FALSE.



Allowing Additional Characters

If you need to allow other characters along with the digits and dashes, you can extend the formula with additional nesting of the SUBSTITUTE function.

Example: Allowing Spaces

If you would like to permit both spaces and dashes in your phone number, modify the formula as follows:

[[See Video to Reveal this Text or Code Snippet]]

This will allow entries like 123-456-7890 or 123 456 7890.

Example: Allowing Parentheses

To also allow parentheses, use this formula:

[[See Video to Reveal this Text or Code Snippet]]

Now, your data validation will accept formats like (123) 456-7890.



Conclusion

By following the steps outlined above, you can effectively create a robust data validation rule in Excel that limits input to digits and dashes while providing flexibility for additional characters like spaces or parentheses. This method ensures that users adhere to the required phone number format without the need for complex VBA coding.

Now go ahead and implement these straightforward steps to enhance the accuracy of your phone number entries in Excel!