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:
=AND(LEN(B13) = 9, ISNUMBER(VALUE(B13)), LEFT(B13, 1) = "0")

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.
Compare items
  • Total (0)
Shopping cart