Data Validation

[cubswin2099]

hello all...

I am an Excel novice (understatement) and need some help. I am hoping that the experts here can provide exactly that...help.

I am looking for some data validation help. I need to keep the text in a cell under 30-characters (I can do this from drop down) but also need to keep special characters out of these same cells (~!@#$%^&*()_+=/><, etc).

How do I do this?

excel 2007

Humbly - Richard

 

[T. Valko]

hello all...

I am an Excel novice (understatement) and need some help. I am hoping that the experts here can provide exactly that...help.

I am looking for some data validation help. I need to keep the text in a cell under 30-characters (I can do this from drop down) but also need to keep special characters out of these same cells (~!@#$%^&*()_+=/><, etc).

How do I do this?

excel 2007

Humbly - Richard

So what kind of characters are permitted? Do you want to allow just the letters A thru Z?

 

[cubswin2099]

So what kind of characters are permitted? Do you want to allow just the letters A thru Z?

Just a-z (cap and small) and 1-20

 

[T. Valko]

Just a-z (cap and small) and 1-20

OK, it might be better if you can show us SEVERAL examples that would be permitted.

 

[cubswin2099]

OK, it might be better if you can show us SEVERAL examples that would be permitted.

It is for inventory so a typical entry would be

2 Quick disconnect fittings
1 Modular gas valve
26 feet PVC pipe

does this help?

 

[T. Valko]

It is for inventory so a typical entry would be

2 Quick disconnect fittings
1 Modular gas valve
26 feet PVC pipe

does this help?

Yeah, a bit.

Let's see if we can break this down to specific rules that have to be followed.

If the first characters have to be numbers then we have to narrow it down to a specific range of numbers. In your other reply you said:

Just a-z (cap and small) and 1-20

OK, but in your samples you have a number 26.

After the first characters that are numbers allow only the letters A thru Z (case not a factor) and spaces.

Is that about right?

We're going to need to get that number rule nailed down!

 

[cubswin2099]

Yeah, a bit.

Let's see if we can break this down to specific rules that have to be followed.

If the first characters have to be numbers then we have to narrow it down to a specific range of numbers. In your other reply you said:


OK, but in your samples you have a number 26.

After the first characters that are numbers allow only the letters A thru Z (case not a factor) and spaces.

Is that about right?

We're going to need to get that number rule nailed down!

Wow....sorry.

1 - 20 will suffice. We will never use a number above that...guess I should read your tag line, eh? (KISS)

 

[cubswin2099]

Wow....sorry.

1 - 20 will suffice. We will never use a number above that...guess I should read your tag line, eh? (KISS)

So yes, all letters (a-z) and the numbers 1-20

 

[T. Valko]

So yes, all letters (a-z) and the numbers 1-20

The more I think about this the more doubts I have about being able to achieve it.

For example, how can we possibly stop someone from entering something like this:

10 AA AA AA AA AA AA AA

We can craft a rule that allows only certain numbers within a range of numbers and only allows the letters of the alphabet and only allows so many characters in total but...

How do we stop an entry like that above? It falls within the rules.

Unless, maybe you're wanting something that's not quite what we call data validation?

 

[cubswin2099]

The more I think about this the more doubts I have about being able to achieve it.

For example, how can we possibly stop someone from entering something like this:

10 AA AA AA AA AA AA AA

We can craft a rule that allows only certain numbers within a range of numbers and only allows the letters of the alphabet and only allows so many characters in total but...

How do we stop an entry like that above? It falls within the rules.

Unless, maybe you're wanting something that's not quite what we call data validation?

The above wouldn't actually be a problem. I would still need the max for characters set at 30, and thus they could write:

20 aa aa aa aa aa aa aa aa aa

This would easily be caught by a manager skimming through prior to uploading to the billing system

This formula would be on a log with as many as 40000 line items and thus searching for over 30, and special characters is taking us forever! Our system will not allow us to upload 30+ or special....but it doesn't tell us which lines were unsuccessful...hope this makes sense...