How to Use Data Validation for a 9-Digit Number or N/A in WPS Spreadsheets
Question details
The user needs to configure Data Validation in WPS Spreadsheets so a selected range accepts either a nine-character value or the exact text N/A, and rejects all other entries.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Setting up a worksheet rule that allows only a 9-digit number or N/A in each target cell.
- Observed behavior
- The target cells should accept entries with nine characters or the exact text N/A, and block any other value.
Open your workbook in WPS Spreadsheets and decide which cell range will use the rule. Note the first cell in that range because the custom formula must be written relative to that cell, for example B2.
Create a Custom Data Validation Rule with OR and LEN
Use this method when you need one rule to allow either a 9-digit number or the exact text N/A in a selected range.
Data Validation evaluates a formula for each cell in the selected range. The OR function returns TRUE when either LEN(B2)=9 or B2="N/A" is met, so only those two entry types are accepted.
Open your WPS Spreadsheets workbook and select the cell or range where the validation should apply, for example B2:B100.
Click the Data tab on the ribbon, then click Validation or Data Validation in the Data Tools group.
In the Settings tab, open the Allow drop-down and choose Custom.
In the Formula box, enter =OR(LEN(B2)=9,B2="N/A"), replacing B2 with the first cell of your selected range.
Click OK. Type a nine-digit value and N/A to confirm they are accepted, then enter another value to confirm it is rejected.
Set Up Custom Data Validation in WPS Spreadsheets
WPS Spreadsheets includes the same Data Validation feature as Excel, so you can apply a custom OR/LEN formula to allow only a 9-digit number or N/A without switching tools.
- 1. Download WPS Office: Go to the official WPS Office website, download the free installer, and install WPS Office on your computer.
- 2. Open your workbook: Launch WPS Spreadsheets and open the Excel workbook you want to edit, or create a new spreadsheet.
- 3. Select the data range: Select the cells that should accept only a 9-digit number or N/A, for example B2:B100.
- 4. Apply custom validation: Click Data > Validation, choose Allow: Custom, and enter =OR(LEN(B2)=9,B2="N/A").
- 5. Confirm the rule: Click OK and test the range with a valid nine-digit value, the text N/A, and an invalid entry.
Frequently Asked Questions
Does the formula check for exactly 9 digits or 9 characters?
The formula =OR(LEN(B2)=9,B2="N/A") uses LEN, so it checks for nine characters, including letters and spaces. If you need a strict nine-digit numeric entry, combine LEN with ISNUMBER or format the cells as Text and use a more specific custom formula.
Is the N/A check case-sensitive?
In WPS Spreadsheets, comparing text in Data Validation is generally not case-sensitive, so n/a and N/A are both accepted by B2="N/A".
How do I apply the rule to an entire column?
Select the whole column, note which cell is active, and use that cell reference in the formula. For example, if you select column B and the active cell is B1, use =OR(LEN(B1)=9,B1="N/A").
Can I show a warning instead of rejecting invalid entries?
Yes. In the Data Validation dialog, open the Error Alert tab, set the Style to Warning or Information, and enter a title and message. This lets users decide whether to keep an invalid entry after seeing the alert.
