1 min readfrom Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community

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

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!

submitted by /u/ana_Lu3
[link] [comments]

Want to read more?

Check out the full article on the original site

View original article

Tagged with

#Excel alternatives for data analysis
#generative AI for data analysis
#natural language processing for spreadsheets
#big data management in spreadsheets
#conversational data analysis
#rows.com
#real-time data collaboration
#intelligent data visualization
#AI formula generation techniques
#Excel compatibility
#financial modeling with spreadsheets
#data validation
#custom formula
#form
#restriction
#validation rule
#invoices
#digitization process
#avoid letters
#certain letters