Restrict the information that can be written in a range

[huorsa]

I want to restrict the information that can be written in a range. Per example in A1, I want just numbers but no more than 10 characters and A2 I want alphanumeric but no more than 60 characters and also don't allow apecial characters like "," "%", "&".

Also if it is possible to show a message when you write a character not allowed.


Any ideas??????


Hugo

 

[Scott R]

For A1 use Data Validation, select "Custom" and enter this in "Formula":
=AND(ISNUMBER(A1),LEN(A1)<=10)

You can enter a message and/or error alert as well.

Determining whether an entry in A2 is alphanumeric is beyond me.

 

[Guest]

On 2002-02-22 17:14, Scott R wrote:
For A1 use Data Validation, select "Custom" and enter this in "Formula":
=AND(ISNUMBER(A1),LEN(A1)<=10)

You can enter a message and/or error alert as well.

Determining whether an entry in A2 is alphanumeric is beyond me.

Scott:

Can you be more specific, I mean what is "data Validation" are you talking about excel?????

Hugo

 

[Guest]

On your menu bar, click tools -> data validation. Select formula on the first of three tabs and enter the formula. The second tow tabs are the message prompts.

Cheers

 

[Aladin Akyurek]

On 2002-02-22 13:38, huorsa wrote:
I want to restrict the information that can be written in a range. Per example in A1, I want just numbers but no more than 10 characters and A2 I want alphanumeric but no more than 60 characters and also don't allow apecial characters like "," "%", "&".

Also if it is possible to show a message when you write a character not allowed.


Any ideas??????


Hugo

What is desired wrt A1 is easy (as Scott R noted), wrt A2 complicated in that it cannot be handled directly from within the Data Validation. In what follows I'll assume A1 and A2 to be points of data entry by users, say, in Sheet1.

Sheet1:

Activate A1;
Go to the Name Box on the Formula Bar, type an appropriate name, e.g., NumEntry, followed by enter.

Activate A2;
Go to the Name Box on the Formula Bar, type an appropriate name, e.g., AlphaNumEntry, followed by enter.

Insert a new worksheet or rename an existing empty worksheet BBoard (from Blackboard).

BBoard:

In B1 enter: Is NumEntry OK?
In B2 enter: Is AlphaNumEntry OK?

In C1 enter:

=AND(ISNUMBER(NumEntry),LEN(NumEntry)<=10)+0

Activate C1; Go to the Name Box on the Formula Bar, type NumEntryOK?, followed by enter.

In C2 enter:

=(SUMPRODUCT((ISNUMBER(MATCH(CODE(MID(UPPER(AlphaNumEntry),ROW(INDIRECT("1:"&LEN(AlphaNumEntry))),1)),{48,49,50,51,52,53,54,55,56,57,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90},0)))+0)=LEN(AlphaNumEntry))+0

Activate C2; Go to the Name Box on the Formula Bar, type AlphaNumEntryOK?, followed by enter.

Select C1:C2;
Activate Format|Cells;
On the Number tab, choose Custom;
Enter for Type:

[=0]"No";[=1]"Yes";General

Back to Sheet1:

Activate A1, the cell we named NumEntry;
Activate the option Data|Validation;
On the Settings tab, choose Custom for Allow;
Enter what follows in the Formula box:

=NumEntryOK?=1

Click OK.

Activate A2, the cell we named AlphaNumEntry;
Activate the option Data|Validation;
On the Settings tab, choose Custom for Allow;
Enter what follows in the Formula box:

=AlphaNumEntryOK?=1

Click OK.

In the last two steps, you have the choice to set up a message on the Alerts tab of the Data Validation Window to tell the user what you expect them to enter in the data entry cells.

Aladin