Function Problems

How to Create Excel Data Validation for 8- or 9-Digit Numbers

Tauseeq Magsi Oct 10, 2026 869 views

Question details

The user needs to create a custom data validation formula that strictly restricts cell input to 8-digit numbers starting with 6, or 9-digit numbers starting with 5.

How to Set Up Excel Data Validation for 8- or 9-Digit Numbers

Product: Excel
Device & OS: not provided
Scenario: Setting up strict data entry rules for specific identification numbers, product codes, or account numbers in a spreadsheet to prevent manual typing errors.
Observed behavior: The target cells accept input only if the value is an 8-digit number beginning with 6 or a 9-digit number beginning with 5, rejecting all other formats.

Before you start

Before applying your custom data validation rule, identify the first cell in the range you plan to validate (e.g., A1 or A2), as this cell reference is crucial for your custom formula to work correctly across the entire selection.

Solution 1Recommended

Use a Custom Formula with ISNUMBER, LEN, and LEFT Functions

This is the most straightforward method. It uses text and length functions to verify both the starting digit and the exact character count of the number entered.

By combining the ISNUMBER, LEN, and LEFT functions inside an AND/OR logic structure, you can enforce multiple conditions simultaneously. The formula checks if the entry is a number, verifies its length, and ensures the first digit matches your exact criteria.

Select the target cells

Highlight the specific cell or range of cells (e.g., A1:A100) where you want to restrict the data entry.

Open Data Validation

Navigate to the Data tab on the Excel ribbon and click on 'Data Validation' in the Data Tools group.

Set the Validation Criteria

In the Data Validation dialog box, under the Settings tab, click the 'Allow' dropdown menu and select 'Custom'.

Enter the custom formula

In the Formula box, type: =AND(ISNUMBER(A1),OR(AND(LEN(A1)=9,LEFT(A1,1)="5"),AND(LEN(A1)=8,LEFT(A1,1)="6"))). Make sure to replace 'A1' with the active first cell of your selected range.

Configure the Error Alert

Switch to the 'Error Alert' tab, choose 'Stop' as the style, and type a customized error message such as 'Please enter a valid 8-digit number starting with 6 or 9-digit number starting with 5.' Click OK.

Use a Custom Formula with ISNUMBER, LEN, and LEFT Functions

Validation Active: Your cells will now automatically reject any entries that do not meet your precise length and starting digit requirements.

Solution 2

Use Mathematical Functions (TRUNC and TEXT)

An alternative method using mathematical truncation to verify the leading digit and length, bypassing the LEFT text function.

Advanced Data Management

Easily Apply Custom Data Validation with WPS Spreadsheet

WPS Spreadsheet offers powerful, user-friendly data validation tools that fully support complex logical formulas. You can set up exact input restrictions to maintain high data accuracy in just a few clicks.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open your workbook where data restrictions are needed.
2. Select your data range: Highlight the specific cells or columns that require validation.
3. Access Data Validation: Navigate to the Data tab on the top ribbon and click the Data Validation icon.
4. Enter your custom rule: Choose 'Custom' from the dropdown menu and paste your validation formula into the formula field.
5. Save and test: Set up an error alert if needed, click OK, and test the cells to ensure your restrictions are working.

Fully compatible with Microsoft Excel (.xlsx) formulas and validation rules.Supports complex custom formulas including ISNUMBER, LEN, and LEFT.Lightweight, fast, and free to use for daily data management tasks.Intuitive interface for configuring input messages and error alerts.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my custom data validation formula reject valid numbers?

This usually happens when the cell reference in your formula does not match the active first cell of your highlighted range. For instance, if you highlight from A2 to A100, your formula must use A2 (not A1) as its base reference.

Can I apply this validation rule to an entire column?

Yes. Click the column letter at the top (e.g., 'A') to select the entire column, then open Data Validation. Make sure your custom formula references the very first cell in that column (A1).

How do I show a customized error message when a user types the wrong number?

While in the Data Validation dialog box, switch to the 'Error Alert' tab. Ensure 'Show error alert after invalid data is entered' is checked, select a style like 'Stop', and type your customized Title and Error message before clicking OK.

How can I find invalid data that was entered before I applied the rule?

You can highlight previously entered incorrect data by going to the Data tab, clicking the small arrow next to Data Validation, and selecting 'Circle Invalid Data'. Red circles will appear around any numbers that don't match your new criteria.

Excel & Data Problems