200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
I want a validation which allow me to allow only enter characters (a-z A-Z) and space with maximum 20 characters.
Exapmle:
shreyas Doshi(Allow)
Shreyasdoshi(Allow)
Shreyas123(Not Allowed)
Shreyas 123(Not Allowed)
ssssssssssssssssssss(Allowed)
sssssssssssssssssssssssssssss(not allowed)
Install this UDF:
Function KillNumbers(text_value As Range)
ValueInput = text_value.Value
ValueResult = ""
n = Len(ValueInput)
For i = 1 To n
InputCharacter = Mid(ValueInput, i, 1)
If InputCharacter Like "[A-Za-z]" Then
ValueResult = ValueResult & InputCharacter
End If
Next i
KillNumbers = ValueResult
End FunctionAnd this UDF:
Function KillText(text_value As Range)
ValueInput = text_value.Value
ValueResult = ""
n = Len(ValueInput)
For i = 1 To n
InputCharacter = Mid(ValueInput, i, 1)
If InputCharacter Like "[0-9]" Then
ValueResult = ValueResult & InputCharacter
End If
Next i
KillText = ValueResult
End FunctionThese UDF's are explained in more detail in our tutorial: Excel Function to Remove All Text OR All Numbers from a Cell
Then use a Data Validation custom formula like this:
=NOT(OR(len(A1)>KillNumbers(A1),LEN(A1)>20,KillText(A1)>0))