# Allow Only character and space with max 20 characters.

0

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)

0

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 Function``````

And 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 Function``````

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

### Discussion

I dont know what UDF, I want Custom validation formula that I can directly put in excel and it will work for me
Shreyas Sep 23, '16 at 4:03 am
Read the tutorial to which I put a link and it will explain everything about UDFs and then just use the single formula that I included in the answer and you can put that into Excel.
don (rep: 1492) Sep 26, '16 at 12:07 pm