Data Validation - No Alpha

[OpenTage]

Hi!

I am looking for formula i can use in the Data Validation tool in Excel.

There is a type of field i want to apply rules to:

1. Numeric & Symbols (no alpha)

I am at a complete loss, tried several google searches to no avail. Numeric only, or Text only seems to be easy, but removing just 1 character type and leaving the other 2 seems tricky.

I'd like to do this through the Excel Data Validation tool, rather than any sort of add-on.

Can anyone help?

Is there perhaps a way I can state =AND(ISNUMBER(A1) and then list all the characters to allow? !@#$%^&*-()'";:/?.><, ?

 

[OpenTage]

There has to be a better method that =AND(ISERROR(FIND("a",A1)),ISERROR(FIND("b",A1))) and then repeat for every letter of the alphabet, upper and lower case


--Also, Data validation in excel wont let me enter a formula long enough to include even just the lowercase Alpha characters

=AND(ISERROR(FIND("a",N14)),ISERROR(FIND("b",N14)),ISERROR(F‌IND("c",N14)),ISERRO‌R(FIND("d",N14)),ISE‌RROR(FIND("e",N14)),‌ISERROR(FIND("f",N14‌)),ISERROR(FIND("g",‌N14)),ISERROR(FIND("‌h",N14)),ISERROR(FIN‌D("i",N14)),ISERROR(‌FIND("j",N14)),ISERR‌OR(FIND("k",N14)),IS‌ERROR(FIND("l",N14))‌,ISERROR(FIND("m",N1‌4)),ISERROR(FIND("n"‌,N14)),ISERROR(FIND(‌"o",N14)),ISERROR(FI‌ND("p",N14)),ISERROR‌(FIND("q",N14)),ISER‌ROR(FIND("r",N14)),I‌SERROR(FIND("s",N14)‌),I

 

[NARAYANK991]

Hi ,

A simpler statement of the problem might help.

1. What characters are allowed ? Just this will imply that all other characters are not allowed.

Just list down all the characters that are allowed.

2. What will be the data entry in the cell ? Data validation happens only when data is entered into a cell and the ENTER key or any cursor movement key is pressed.

Indicate whether the data entry will be a single character or multiple characters ; if it is the latter , then even the presence of one disallowed character in a data which is 200 characters long will have to be detected.

Being as thorough as possible in your problem description and uploading a workbook with enough data in it , are the surest ways to get a solution.

When you post a problem , leave it to those who answer to use what ever formula they wish to ; it is very unlikely that any member who has thought of a solution to your problem will first try to answer why your formula does not work. That can come later.

Also indicate whether you want a formula-only solution or can accept a VBA-based solution.

Narayan

 

[Belleke]

I never work with formulas, can't help you there, here a VBA example
See attached

 

[OpenTage]

Hi ,

A simpler statement of the problem might help.

1. What characters are allowed ? Just this will imply that all other characters are not allowed.

Just list down all the characters that are allowed. - This list is too long for the Data Validation formula field; and arrays are not allowed in data validation

2. What will be the data entry in the cell ? Data validation happens only when data is entered into a cell and the ENTER key or any cursor movement key is pressed.

Indicate whether the data entry will be a single character or multiple characters ; if it is the latter , then even the presence of one disallowed character in a data which is 200 characters long will have to be detected.

Being as thorough as possible in your problem description and uploading a workbook with enough data in it , are the surest ways to get a solution.

When you post a problem , leave it to those who answer to use what ever formula they wish to ; it is very unlikely that any member who has thought of a solution to your problem will first try to answer why your formula does not work. That can come later.

Also indicate whether you want a formula-only solution or can accept a VBA-based solution.

Narayan

 

[OpenTage]

Thanks All - i found a result through another forum

=NOT(ISNUMBER(LOOKUP(2^15,SEARCH(CHAR(ROW(INDIRECT("65:90"))),A1))))