Function Problems

How to Use Excel Data Validation for Text Starting With P or I

Guest Writer Sep 27, 2026 870 views

Question details

The user needs to create an Excel data-validation rule that strictly limits cell entries to text strings beginning with specific uppercase letters, namely 'P' or 'I'.

How to Restrict Excel Input to Text Starting With Specific Letters

Product: Excel
Device & OS: not provided
Scenario: Setting up strict data entry controls in a spreadsheet to ensure users only input allowed prefixes.
Observed behavior: Standard custom formulas using the OR function may trigger an error in the data-validation field, requiring an alternative robust formula.

Before you start

Identify the target cell or range (for example, J9) where you want to apply the data validation rule before writing your custom formula.

Solution 1Recommended

Use SUM and EXACT Array Formula (Recommended)

This formula prevents errors in the data validation field and precisely checks multiple allowed starting letters using an inline array.

This method uses an inline array to check the first character against multiple specific letters at once. It avoids the formula error sometimes triggered by the OR function in the validation dialog of certain Excel versions.

Select the target cell

Click on the cell you want to restrict, for example, J9.

Open Data Validation

Navigate to the Data tab on the ribbon and click on Data Validation.

Set Custom Rule

In the Allow drop-down list, select Custom. In the Formula box, enter the formula `=SUM(EXACT(LEFT(J9,1),{"P","I"})*1)`.

Apply and test the rule

Click OK to save. Try entering words starting with 'P', 'I', and other letters to confirm the cell only accepts the specified prefixes.

Use SUM and EXACT Array Formula (Recommended)

Case Sensitivity: The EXACT function ensures the check is case-sensitive. If you type lowercase 'p' or 'i', the input will be rejected.

Solution 2

Use OR and EXACT Formula

A traditional alternative formula using the OR function, which works well in many standard versions of Excel.

Advanced Data Validation in WPS

Easily Set Up Custom Data Validation Rules with WPS Spreadsheet

WPS Spreadsheet fully supports advanced array formulas and custom data validation rules, allowing you to accurately control data entry and ensure spreadsheet integrity without compatibility errors.

1. Select your range: Open your document in WPS Spreadsheet and select the cells you want to restrict.
2. Access Data Validation: Navigate to the Data tab and click the Data Validation button.
3. Enter the formula: Choose 'Custom' from the Allow dropdown menu, input your custom formula, and click OK to apply the restrictions.

Fully compatible with Microsoft Excel .xlsx formats and complex formulasIntuitive Data Validation interface for quick setupFree, lightweight, and fast alternative for data managementFamiliar UI guarantees a seamless migration

microsoft office alternative - wps office

Frequently Asked Questions

How can I make the data validation case-insensitive?

If you want to allow both uppercase and lowercase 'P' or 'I', you can omit the EXACT function and simply use the OR function with LEFT. For example: `=OR(LEFT(J9,1)="P", LEFT(J9,1)="I")`. Standard logical operators in Excel are not case-sensitive.

Can I add more allowed starting letters to this formula?

Yes. Using the recommended SUM formula, you can easily add more letters to the inline array. For example, to allow P, I, and X, modify the array part to `{"P","I","X"}`.

Why does the OR function sometimes fail in the Data Validation window?

In some Excel versions, the Data Validation formula parser struggles with certain array or complex logical evaluations within the OR function. Using the SUM function combined with mathematical operations (like multiplying by 1) bypasses this limitation.

How do I apply this validation rule to an entire column?

Select the entire column (e.g., Column J) before opening Data Validation. When entering the formula, ensure you use the relative reference of the active cell (usually the first cell in the selection, like J1), so the formula correctly adapts for each cell down the column.

Excel & Data Problems