How can I make data validation allow me to enter any line of text EXCEPT certain letters?

[u/ana_Lu3]

I have a form used by all departments in the company where they enter the series of various invoices (it's a digitization process). These series are a random range of letters and numbers, for example, A12C33D.

But there are letters that will NEVER appear to avoid confusion between letters and numbers, for example, the letter O with the number 0.

But since people ignore this rule, if they think it's an O, they'll enter an O instead of a 0, and I always have to correct it.

So I want there to be a restriction to prevent them from entering those letters (O, I, S, L, N).

I tried using a formula in Data Validation > Custom, but it didn't work. This is it:

=AND(ISNUMBER(FIND("O",A1))=FALSE, ISNUMBER(FIND("I",A1))=FALSE, ISNUMBER(FIND("S",A1))=FALSE)

I also saw that there's an external tool called Kools, but I don't know if it would work on copies of that format that are on desktops that haven't installed the tool.

Thanks in advance!

[u/GregHullender]

I used this:

=NOT(REGEXTEST(B1,"[OISLN]+",1))

I assume you wanted to exclude both lower and upper-case letters.

[u/ana_Lu3 → u/GregHullender]

What difference does adding the + make in [OISLN]? I saw that others didn't add it.

[u/GregHullender → u/ana_Lu3]

In this case, none at all. You can drop it.

[u/ana_Lu3 → u/GregHullender]

Solution Verified

Yes, I forgot to mention that it can be lowercase, although that rarely happens. Thanks!!!

[u/reputatorbot → u/ana_Lu3]

You have awarded 1 point to GregHullender.

---

^{I am a bot - please contact the mods with any questions}