February 5, 2026•1 min read•from 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?

Our take

Are you tired of constantly correcting data entry mistakes due to confusion between letters and numbers? In your digitization process, ensuring the integrity of invoice series is crucial, especially when certain letters like O, I, S, L, and N can lead to costly errors. The good news is that you can set up data validation to restrict these entries, creating a smoother experience for all departments. Ready to learn how to implement this effectively?

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]

Read on the original site

Open the publisher's page for the full experience

View original article →

Data validation is not displaying error alert for blank cells even when "Ignore blank" is not checkedI have a table including columns for the numbers of year, month, and day. The month and day don't have to be specified, but if they're not I want the values to be 0 rather than left blank. I've set up data validation to limit the allowable values to lists (one for month, one for day) that don't contain any blanks. "Ignore blank" is not checked, and I've set up an error alert to be displayed when invalid data is entered. It works correctly if I enter any value that's not in the table. But if I leave the cell blank, the alert isn't displayed. See screenshot of relevant cells and validation settings below. Here are things I've checked and alternatives I've tried: The fields are defined as numbers. I used ISBLANK in another cell to confirm that the cells really are blank. It happens whether I delete the value in an existing row or enter a new row and leave the cell blank. Entering the allowable values as whole numbers between 0 and 12 doesn't display the alert. Entering the allowable values as a list with the source as the range in the Month values column (which has no blanks) doesn't display the alert. Entering the allowable values as a list with the values directly in the Source field (which has no blanks at the beginning, the end, or between items) doesn't display the alert, but I do get an error indicator in the cell, saying "The value in this cell is invalid or missing," Presence or absence of an in-cell dropdown doesn't affect the behavior. The file is one I've had for several years and tinkered with a lot, only recenlty converted it to a table, and only after that added the validation. In case there was something left somewhere after all those changes that was causing the behavior, I've created the smaller sample version in the screenshot, entering all the data, formulas, and validation from scratch. The problem remains. Other potentially useful information: I'm using Excel 365 desktop app. The date elements are in separate columns because many of the dates are before 1900 and Excel can't handle them correctly. The years alone are sufficient for my calculations at this point, but it's helpful to have the have the month and day when they're known. I've used data validation to produce error alerts for blank cells many times before and this is the first time this has happened. Any ideas? Anything I've overlooked? https://preview.redd.it/u9w14x8eh2qg1.jpg?width=861&format=pjpg&auto=webp&s=c9215b68d81b1d9780998f159e89e1819d883dda submitted by /u/fastauntie [link] [comments]

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

Related Articles