• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Custom Data Validation to Allow only Specific Characters

Cruiser

Member
I want to use Custom Data Validation to only allow the alpha & numeric characters plus a few selected special characters. I found the following on the extend office site and added my special characters:

[=ISNUMBER(SUMPRODUCT(SEARCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),"0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ&<>"))) ]

It works. However, I need this validation in over 1,000 cells. I am concerned that putting the volatile finction INDIRECT in all of those cells will bog down my workbook.

  1. How would you modify this formula to allow only the listed characters without using INDIRECT?
  2. How do I add space (“ “) to my list of allowed characters?
 

NARAYANK991

Excel Ninja
Hi ,

Have you tried using a fixed length , as in :

=ISNUMBER(SUMPRODUCT(SEARCH(MID(A1,ROW(1:999),1),"0123456789abcdefghijklmnopqrstuvwxyz ABCDEFGHIJKLMNOPQRSTUVWXYZ&<>")))

The space character has been inserted in between z and A.

Narayan
 

Cruiser

Member
Thanks Narayan. I was able to further refine it by doing away with MID and ROW so that it just checks cell A1.

To anybody interested: This does not catch asterisks (*) or question marks (?)
I had to include ISERR tests for these characters.
 

NARAYANK991

Excel Ninja
Hi ,

Good catch ; the reason is that they have a special significance as wildcard characters , and hence will match any character.

You can revise the formula as follows :

=ISNUMBER(SUMPRODUCT(SEARCH(MID(SUBSTITUTE(SUBSTITUTE(A1,"?","|"),"*","|"),ROW($1:$999),1),"0123456789abcdefghijklmnopqrstuvwxyz ABCDEFGHIJKLMNOPQRSTUVWXYZ&<>")))

Narayan
 

Cruiser

Member
Thanks Narayan. I had used:

=AND(ISNUMBER(SUMPRODUCT(SEARCH(MID(A1,ROW($1:$30),1),"0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ&<>"))),(IF(ISERR(FIND("?",A1))+ISERR(FIND("*",A1))=2,TRUE,FALSE)))

Works perfectly.Your formula using SUBSTITUTE is a bit shorter, but I already have all of my data validation set up with the above. Do you see an advantage to redoing all my validation with the SUBSTITUTE version, or am I just as well to leave it as I have it?
 

Haseeb A

Active Member
Hello Cruis,

SEARCH is NOT case sensitive. So only either upper or lower alphabet will suffice in the formula.

For the ? & * you can use tilde ~ before text. This way you are telling to excel that look for exactly correct values in the array. If you enter ? or * look for this exact value not as wildcard.

The formula is,

=ISNUMBER(SUMPRODUCT(SEARCH("~"&MID(A1,ROW($1:$99),1),"0123456789 abcdefghijklmnopqrstuvwxyz&<>")))

Note: There is a 'space' between 9 and a
 

Cruiser

Member
Beautiful! Thanks Haseeb. One thing to note for others, this returns TRUE if the cell is blank. This did not matter to me because I had to add certain LEN requirements anyway.
 

riplen

New Member
Hello Cruis,

SEARCH is NOT case sensitive. So only either upper or lower alphabet will suffice in the formula.

For the ? & * you can use tilde ~ before text. This way you are telling to excel that look for exactly correct values in the array. If you enter ? or * look for this exact value not as wildcard.

The formula is,

=ISNUMBER(SUMPRODUCT(SEARCH("~"&MID(A1,ROW($1:$99),1),"0123456789 abcdefghijklmnopqrstuvwxyz&<>")))

Note: There is a 'space' between 9 and a
Thank you so much for this formula Haseeb A., it works perfectly.
Do you know how/ where to integrate in this formula another formula to limit the text length (equal or less then...) of characters in a cell?
 

narendra123

New Member
I want to use Custom Data Validation to only allow the alpha & numeric characters plus a few selected special characters. I found the following on the extend office site and added my special characters:

[=ISNUMBER(SUMPRODUCT(SEARCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),"0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ&<>"))) ]

It works. However, I need this validation in over 1,000 cells. I am concerned that putting the volatile finction INDIRECT in all of those cells will bog down my workbook.

  1. How would you modify this formula to allow only the listed characters without using INDIRECT?
  2. How do I add space (“ “) to my list of allowed characters?
I was in the same situation a few days back and I used an almost similar approach. But I avoided the volatile INDIRECT function, by using the INDEX function.
If a fixed length like 999 is sufficient, use it.
But, if you want the upper limit to be based on something else, like in this case, the string's length, then you can replace the part INDIRECT("1:"&LEN(A1)) with A1:INDEX(A:A,LEN(A1)).
 
Top