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.
[=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.
- How would you modify this formula to allow only the listed characters without using INDIRECT?
- How do I add space (“ “) to my list of allowed characters?