Custom Validation for Specific Number Entry Criteria

Custom Validation for Specific Number Entry Criteria. To enforce specific criteria for data entry—allowing only numerical values between 0 and 9, a length of exactly 9 digits, and permitting the entry to begin with zero—follow these steps:

Custom Validation for Specific Number Entry Criteria

1. Select the target range (e.g., A17:A20).
2. Navigate to the ‘Data’ tab and click on ‘Data Validation’ > ‘Settings.’
3. Choose the ‘Custom’ category for validation criteria.
4. Enter the following formula:

Explanation of Validation Criteria:

• LEN(B13) = 9: Validates for exactly 9 characters.
• ISNUMBER(VALUE(B13)): Ensures that only numerical values are entered (0-9).
• LEFT(B13, 1) = "0": Requires the entry to start with zero.

This validation setup will trigger an error if:

• The entry is not exactly 9 digits.
• Any character is not a number (0-9).
• The entry doesn’t start with zero.

Implementing this validation will restrict entries that don’t adhere to these criteria, ensuring only valid numerical data is accepted within the specified constraints.