Excel Data Validation for Text: Rules and Examples

How-to basics · Updated November 18, 2026

In Excel for Microsoft 365 on Windows, use Data > Data Validation to limit text entries by character count, provide a dropdown of permitted words, or apply a custom formula. Choose Text length for simple length rules; choose Custom when you need to check that a value is text or meets several conditions.

What text validation does in Excel

Data validation is a rule attached to one or more worksheet cells. It can guide people as they enter information and, depending on the rule and error-alert setting, reject entries that do not meet the rule. For text, common uses include limiting a description to 40 characters, requiring a five-character code, or offering a controlled set of statuses such as Open, Pending, and Closed.

Validation is not the same as formatting. Formatting changes how a value looks, while validation checks an attempted entry against a condition. Nor is it a complete security measure: users can sometimes paste or fill data in ways that do not trigger the same prompts as typing. Treat it as a useful input control, and check important data separately.

A frequent misconception is that the Text length option guarantees a cell contains text rather than a number. It sets a character-count condition; it is not a general “text only” data type switch. If the distinction matters, use a custom formula that checks the value with ISTEXT.

Set a minimum or maximum character count

For a simple length limit, select the cells to control, then open Data > Data Validation. On the Settings tab, set Allow to Text length. Choose a condition such as less than or equal to, between, or equal to, and enter the required number or numbers. For example, choose “less than or equal to” and enter 40 to limit an entry to 40 characters.

  1. Select the input cells, such as B2:B100. Select B2 first so Excel uses it as the top-left cell for any relative references in a custom rule.
  2. Choose Data > Data Validation, then select Text length under Allow.
  3. Choose the comparison, enter the character limit, and set the alert options if needed.
  4. Select OK, then try a short and an over-limit entry to confirm the rule behaves as intended.

Use between when entries need both a minimum and maximum length, such as 2 to 40 characters. Use equal to when every entry must have the same length. Excel counts spaces and punctuation as characters, so “AB 12” has five characters, including the space. A blank cell may still be permitted when Ignore blank is selected; that setting is useful for optional fields but does not make a field required.

Offer a dropdown of allowed text

If the goal is to standardize a category or status, a list is usually better than a length rule. Select the destination cells, open Data > Data Validation, set Allow to List, and provide a source. Turn on In-cell dropdown if users should be able to choose an item from a menu.

For a short, fixed set of options, type the values into the Source box separated by commas, for example Open,Pending,Closed. If the options may change or are shared across multiple sheets, put them in a worksheet range and use that range as the source. A named range can make a source easier to recognize and maintain. Keep the source values clean: inconsistent spaces or spelling can create entries that look similar but are different to Excel.

A dropdown helps people choose a permitted value, but it does not create multi-select behavior. Standard Data Validation lists accept one selection per cell. Also, a list does not automatically clean up older values if you later remove an option from its source. Review existing entries after changing the permitted choices.

Use a custom formula to check text

Choose Custom under Allow when the rule must check more than character count. In the formula, refer to the top-left cell of the selected range. If the selection begins at A2, use A2 in the formula; Excel adjusts the reference for the other cells in the range.

With the validation range beginning at A2, this formula accepts text entries of no more than 30 characters:

=AND(ISTEXT(A2),LEN(A2)<=30)

Here, ISTEXT checks the value’s type and LEN counts its characters. For instance, the text entry “AB-123” has six characters and passes, while a numeric value does not pass the ISTEXT test. To require a five-character text identifier, use this formula with the same top-left-cell assumption:

=AND(ISTEXT(A2),LEN(A2)=5)

This can help preserve identifiers with leading zeroes, which should be stored as text if those zeroes are significant. Explain the expected format to users; validation cannot infer whether a code such as “00123” is meant as a number or an identifier.

To reject empty text and leading or trailing spaces, select a range beginning at A2 and use:

=AND(ISTEXT(A2),LEN(A2)>0,A2=TRIM(A2))

TRIM removes leading and trailing ordinary spaces and reduces repeated spaces between words, so the equality check rejects text that would change under TRIM. This may be too strict if repeated spaces are meaningful. Also, a formula that allows only text does not necessarily enforce a particular vocabulary; use a list for a fixed set of choices or add a carefully designed formula for a more complex rule.

Configure prompts and error messages

The Data Validation dialog includes Input Message and Error Alert tabs. An input message can tell someone what to enter when they select a cell, such as “Enter a label of up to 40 characters.” It is a prompt, not a rule: the Settings tab determines what is accepted.

On the Error Alert tab, choose a style that matches the task:

  • Stop blocks the invalid entry and asks the user to correct it. Use this when entries must meet the rule.
  • Warning lets the user choose whether to keep the entry or return and correct it.
  • Information displays a notice but lets the user continue.

Give the alert a useful title and a specific explanation. “Enter no more than 40 characters” is more actionable than “Invalid data.” If the alert is disabled, Excel will not show the configured error message for invalid typed entries, so check that setting when a rule appears not to be enforced.

Check existing entries and troubleshoot rules

Adding a rule does not guarantee that every value already in the selected cells complies with it. After setting validation, inspect existing content, especially if the worksheet already contains data. In Excel, Data > Data Validation includes tools to circle invalid data when the command is available for the selected range. You can also select a cell with validation and open the dialog to inspect its settings.

If valid-looking entries are rejected, check these common causes:

  • The formula points to the wrong cell. For a range starting at C5, a custom formula should normally refer to C5, not A1. A relative reference adjusts across the selected range.
  • The field contains a number, not text. A code displayed with leading zeroes may still be numeric. A custom ISTEXT test will reject it until the value is stored as text.
  • Spaces or hidden characters are present. A pasted value may include extra spaces or nonprinting characters. Compare the entry with a cleaned version before changing the rule.
  • Data was pasted or filled into the cells. Some paste and fill operations can bring data in without the expected entry prompt. Recheck the values and validation after importing or pasting.
  • Only part of the range has the rule. Select several cells and inspect their validation settings; copied cells can have different rules from neighboring cells.

To locate cells with validation, use Home > Find & Select > Go To Special, then choose Data Validation. This can help identify which cells are controlled before you edit or clear rules. Be cautious when copying whole cells: copying can also copy validation settings, potentially replacing the destination rule.

Excel for Mac and Excel for the web

In Excel for Microsoft 365 on Mac, Data Validation is also available from the Data tab; dialog layout and some labels can differ slightly from Windows. The same general approach applies: select the cells, open Data Validation, choose a rule type, and configure its conditions. Keyboard shortcuts can differ between Windows and Mac, so using the ribbon is the clearest cross-platform instruction.

Excel for the web also supports data-validation workflows, but the available commands and editing experience can vary from the desktop app. If a workbook’s rule is missing, difficult to edit, or behaves differently online, open it in the desktop version and review the Data Validation dialog there. When sharing a workbook, test the actual entry process in the version recipients will use.

Frequently Asked Questions

How do I limit text to 10 characters in Excel?
Select the cells, choose Data > Data Validation, set Allow to Text length, choose less than or equal to, and enter 10. Set the error alert to Stop if over-limit entries must be rejected.

Can Excel Data Validation allow text only?
Yes. Use a Custom rule with ISTEXT, referring to the first cell in the selected range. For example, with the range beginning at A2, =ISTEXT(A2) checks that an entry is text. Add LEN or other conditions if you also need a length rule.

Can I make a text field required?
Yes. Turn off Ignore blank where appropriate and use a rule that rejects empty values. For a custom text rule, include a positive length requirement, such as =AND(ISTEXT(A2),LEN(A2)>0) for a range beginning at A2. Test how blank cells behave in the workbook before relying on the rule.

Does Data Validation check text that is already in a cell?
Not automatically as a correction step. A new rule may leave existing values untouched. Review the current entries and use Excel’s invalid-data tools where available, then correct any noncompliant values.

Why can someone still paste invalid text?
Validation is designed mainly to control data entry and can be bypassed by some paste, fill, or import workflows. Check the cells after bulk changes, and use additional review or protection measures if consistent data is important.

Choosing the right rule

Use Text length when the requirement is simply a minimum, maximum, or exact number of characters. Use a List for a fixed vocabulary such as department names or statuses. Choose Custom when you must require text, combine conditions, or check a pattern that the built-in options cannot express. Clear prompts, a Stop alert for mandatory rules, and a quick review after pasting make these controls more reliable without making a worksheet difficult to use.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.