How to set text validation?

user_11054439 0
Viewed 79

I want to only allow text input on some cells. I have tried some code, but it allows to input numbers and letters. Any help would be great, thanks.

I tried the istext function and it works. When the input is all numbers it does not allow, but in number/letter case is does not work

  =ISTEXT(D6)
vba excel
2 Answers

This formula used in data validation will only allow alpha(a-z) and no others:

=SUMPRODUCT(--ISNUMBER(SEARCH(MID(A1,ROW(INDIRECT("1:" & LEN(A1))),1),"abcdefghijklmnopqrstuvwxyz")))=LEN(A1)
user_48515900

You need a formula that returns false when there are numbers in the cell, you can normally use:

=COUNT(FIND({0,1,2,3,4,5,6,7,8,9},D6))=0

But in your case, you must place the formula inside a data validation rule, and those don't allow array constants, so you'll have to use repeated Find statements:

=COUNT(FIND("0",D6),FIND("1",D6),FIND("2",D6),FIND("3",D6),FIND("4",D6),FIND("5",D6),FIND("6",D6),FIND("7",D6),FIND("8",D6),FIND("9",D6))
user_17385720
Related