I have a spreadsheet where in one column the users must input alphanumerics, but no special characters, and another column where they should only input numbers. Both of these cases are issues where they have a tendency to input things that shouldnt be there which causes alot of problems with the rest of the spreadsheet, how do i prevent those things from being input into the cells (without using macros)?
Hi,
Re the first part, do you have an exhaustive list of all the special characters that they are not allowed to enter?
Re the second, are there any refinements to that criteria, or are you happy if it's simply 'a' number?
Regards
You can do this using data validation.
However, this can be defeated by copying and pasting into the target cells or by dragging and dropping into the target cells.
You need to better define "alphanumerics". Does that mean a combination of the letters A to Z and the numbers 0 to 9? Does the case of the letters matter? How about the length of the string?
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Well for the first case, i mean just letters and numbers, no )(*#@$&(&^@!#& stuff. Case doesnt matter, length doesnt matter though it probably wont be more than 100 characters.
For the second case i just want it to be a number, nothing else.
if i had to pick which special characters i dont want, it would be...... / _ - , \ ( ) | { } [ ]
Try this...Originally Posted by poof
First, create this named expression...
Goto the menu Insert>Name>Define
Name: String
Refers to: ="ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789"
OK out
Apply the validation...
Let's assume you want this to apply to cell A1.
Select cell A1
Goto the menu Data>Validation
Allow: Custom
Formula:
=AND(SEARCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),String))
Uncheck: Ignore blank
OK out
If you get this warning: "The formula currently evaluates to an error. Do you wish to continue?" Just answer Yes.
If you want to test that formula as a formula in a worksheet cell it must be array entered.
Array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Do you want to allow ANY number like 2.5 or do you just want whole numbers like 2?For the second case i just want it to be a number, nothing else.
If you want ANY number then:
Data>Validation
Allow: Custom
Formula: =ISNUMBER(A1)
If you just want whole numbers then:
Data>Validation
Allow: Custom
Formula: =INT(A1)=A1
Or Data > Data Validation > Allow: Whole numberIf you just want whole numbers then:
Data>Validation
Allow: Custom
Formula: =INT(A1)=A1
Entia non sunt multiplicanda sine necessitate
I entered it as a data validation in the cell as you described, but applied it to E2 instead of A1 like so:
=AND(SEARCH(MID(E2,ROW(INDIRECT("1:"&LEN(E2))),1),String))
and okayd through it, then put some special characters in the cell and they entered fine, so that didnt work.
Then i tried the ctrl shift enter method of putting the formula in the cell but it gave me a circular reference error.
The datavalidation number-only method works fine for my other problem though!
Tell me what cell(s) you want this to apply to and I'll post a sample file.
I2 - I100 specifically
Ok...
Assume you have already created the named expression "String".
Select the entire range I2:I100
Goto the menu Data>Validation
Allow: Custom
Formula:
=AND(SEARCH(MID(I2,ROW(INDIRECT("1:"&LEN(I2))),1),String))
Uncheck: Ignore blank
OK out
If you get this warning: "The formula currently evaluates to an error. Do you wish to continue?" Just answer Yes.
If you want to test that formula as a formula in a worksheet cell it must be array entered. You would enter the formula in some other cell, for example cell J2, not the target cell I2.
Array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Here's a small sample file that demonstrates this.
DataValidation.xls
I have only applied the validation to the range I2:I10 and have entered the array formula in the range J2:J10. Currently, the array formula is returning an error. This is expected.
The range I2:I10 will only allow you to type in strings that contain the letters A-Z, a-z, and/or the digits 0-9.
thank you that worked, but unfortunately the cells im applying it to some have a data validation list in them already, but with one blank so other things can be entered too, so this will only work in so many instances.
You can only apply 1 type of validation to a cell.
A drop down list is a type of data validation.
So, you can either have the drop down list or you can use the formula to restrict the data entry but you can't have both. One or the other.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks