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)

Answer
Discuss

Answers

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

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: 1442) Sep 26, '16 at 12:07 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login