Custom Data Validation to Allow only Specific Characters

[Cruiser]

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]

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]

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]

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]

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]

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]

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]

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]

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