Yes, data validation can allow text only, but not with a single built-in rule. You need to combine a validation rule with a formula that checks whether the entry is text, and the exact approach depends on your spreadsheet app and whether you also want to allow numbers stored as text.
What “Text Only” Actually Means
In spreadsheet terms, “text” is any value that isn’t a number, date, or logical TRUE/FALSE. That includes words, spaces, punctuation, and even numbers that are formatted as text (like a ZIP code with leading zeros). When you set up data validation to allow only text, you’re telling the app to reject anything that Excel or Google Sheets recognizes as a numeric value. This is useful for columns that should contain names, product codes, or comments—where a stray number would break downstream formulas or sorting.
How to Allow Text Only in Excel
Excel has no built-in “text only” validation option, so you use a custom formula. The most reliable formula is:
=ISTEXT(A1)
where A1 is the first cell in your selected range. This returns TRUE for any text entry and FALSE for numbers, dates, or errors. To apply it:
- Select the cells you want to validate.
- Go to Data → Data Validation → Settings.
- Choose “Custom” from the Allow drop-down.
- Enter
=ISTEXT(A1)(adjust the cell reference to the top-left cell of your selection). - Set an input message and error alert as desired, then click OK.
One caveat: ISTEXT returns TRUE for cells that contain only a space or an apostrophe. If you want to reject blank-looking entries, add a check that the cell isn’t empty: =AND(ISTEXT(A1), TRIM(A1)<>"/") (replace the slash with two double-quotes).
How to Allow Text Only in Google Sheets
Google Sheets also lacks a direct text-only rule. Use the same custom formula approach:
- Select your range.
- Click Data → Data validation.
- Under “Criteria,” choose “Custom formula is.”
- Enter
=ISTEXT(A1)(adjust to the active cell). - Set the style for invalid data (reject or warn) and save.
Google Sheets treats numbers stored as text differently from Excel in some cases. For example, a cell containing "123" (typed with an apostrophe) is text, and ISTEXT returns TRUE. However, if you use the default “Text” criterion instead of a custom formula, it will also allow numbers—so the custom formula is the only way to force text-only.
Why Not Use the “Text Length” or “List” Rules?
Some users try to hack around with “Text length” (e.g., minimum 1 character) or a list of allowed characters. These don’t work because:
- Text length still allows numeric entries—it only checks the number of characters.
- List requires you to type every possible allowed value, which is impossible for free-form text.
- Custom formula is the only method that checks the data type itself.
Common Pitfalls and How to Avoid Them
Even with the correct formula, you may run into issues:
- Leading apostrophe: In Excel, typing an apostrophe before a number (e.g.,
'123) forces text.ISTEXTreturns TRUE, so it passes validation. If you want to reject numbers-as-text, you’d need a more complex formula like=AND(ISTEXT(A1), ISNUMBER(--A1)=FALSE)—but that also rejects numeric text that isn’t a number (like “abc”). - Blank cells: By default, validation ignores blanks. If you want to require text in every cell, uncheck “Ignore blank” in the settings.
- Copy-paste overrides: Data validation does not block pasted values that are invalid—it only flags them after the fact. You may need to use “Circle Invalid Data” (Excel) or a conditional formatting rule to visually highlight them.
- Formulas returning text: If a cell contains a formula that returns a number (e.g.,
=1+1),ISTEXTreturns FALSE, so it’s rejected. That’s usually what you want.
Alternatives to Data Validation for Text-Only
If data validation isn’t enough, consider these approaches:
- Conditional formatting: Highlight any non-text cell with a red fill. Use a formula like
=NOT(ISTEXT(A1))applied to your range. This doesn’t block entry but makes errors visible. - Excel Table with a calculated column: Add a helper column that flags non-text entries with
=IF(ISTEXT(A2),"OK","Check"). You can then filter for “Check.” - Google Sheets with Apps Script: For real-time blocking, you can write a simple onEdit trigger that reverts invalid entries. This is more advanced but gives you full control.
Frequently Asked Questions
Does data validation allow text only in Excel?
Yes, but only with a custom formula. Use =ISTEXT(A1) as the validation rule. There is no built-in “text only” option.
How do I allow text and numbers but not dates?
Use a custom formula that excludes dates: =NOT(ISNUMBER(A1)) would reject numbers too. To allow numbers but not dates, use =AND(ISNUMBER(A1), NOT(ISDATE(A1)))—but note that Excel doesn’t have an ISDATE function. You’d need =AND(ISNUMBER(A1), CELL("format",A1)<"D") which is fragile. A simpler approach is to format the column as Text before entry.
Why is my data validation not working when I paste?
Data validation only checks entries typed directly into cells. Pasting values bypasses the validation rule in most cases. To catch pasted invalid data, use conditional formatting or re-validate after pasting.
Can I use data validation to allow only letters (no numbers or symbols)?
Yes, but you need a more complex formula that checks each character. For example, in Excel: =AND(ISNUMBER(SUMPRODUCT(--ISNUMBER(SEARCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),"abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"))))), LEN(A1)>0). This is an array formula and can be slow for large ranges. A simpler alternative is to use a regex in Google Sheets: =REGEXMATCH(A1,"^[A-Za-z]+$").
Does data validation work on merged cells?
It can, but it's tricky. When you apply validation to a merged range, only the top-left cell's validation is active. It's better to avoid merging cells in data-entry columns.