Solved Prevent special characters but allow spaces with validation

December 1, 2018 at 15:40:42
Specs: Windows 7
Hello forum, I am using the following formula in a cell's (F5) data validation to prevent special characters. But it also does not allow spaces. Does anyone know how I can tweak it to allow spaces as well as numbers and letters?

=ISNUMBER(SUMPRODUCT(SEARCH(MID(F5,ROW(INDIRECT("6:"&LEN(F5))),1),"0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ")))


See More: Prevent special characters but allow spaces with validation

Reply ↓  Report •

#1
December 1, 2018 at 17:09:29
✔ Best Answer
Try this:

=ISNUMBER(SUMPRODUCT(SEARCH(MID(F5,ROW(INDIRECT("6:"&LEN(F5))),1)
," "&"0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ")))

See how that works for you.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Reply ↓  Report •

#2
December 2, 2018 at 04:53:28
Thanks again it worked perfectly :-)

Reply ↓  Report •
Related Solutions


Ask Question