CODEWITHSUNDEEP
excelvba
jvargas
jvargas

Reputation: 843

How to set text validation?

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)

Upvotes: 2

Views: 104

Answers (2)

Scott Craner
Scott Craner

Reputation: 152505

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)

Upvotes: 5

Greg Viers
Greg Viers

Reputation: 3523

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))

Upvotes: 3

Related Questions