Data validation for text only in Excel

1
votes

Please suggest me a custom data validation formula which allow text and space only

i.e. true for "John Smith" and "Monty" and false for "12356","45 Smith","John45", "John Smith 5", "78John" etc.

1
excelvalidationexcel-formula
=istext(A1) =not(isnontext(A1)) =not(isnumber(A1) are not working. - Abhinn Wasav
try updating your question instead of posting comments with the update - Younes Regaieg

1 Answers

2
votes

Use this:

=ISNUMBER(SUMPRODUCT(SEARCH("~"&MID(A1,ROW(indirect("1:" &Len(A1))),1),"abcdefghijk lmnopqrstuvwxyz")))