Hi!
OK, if column B contains this formula: =PROPER(A1)
Then the validation formula references column B, so
Change this portion of the formula:
=OR(AND(ISNUMBER(B1),......
To:
=OR(AND(ISNUMBER(--B1),
That will take care of the PROPER issue.
Biff
-----Original Message-----
I am not using data validation.
Column A has all the IDs that where entered by Users. We
have about 3,500
IDs. Column B has the Proper formula and Column C has
your formula u gave
me. Sometimes Users don't use Upper Case, so I am using
Proper just to
convert to Upper Case.
Ruan
"Biff" wrote in message
...
Hi!
Are you using this as a data validation rule via
DataValidation, or, are you simply using this formula
in
a cell to return either TRUE or FALSE?
The reason I ask is that as a DV rule, the formula is
almost to it's length limit of 255 chars. A cell formula
can be 1024 chars long.
I don't understand why you're using PROPER. Can you
provide more detail as to how this is being used?
Biff
-----Original Message-----
Hello Biff,
I have been entering in 10 character numeric values and
the result is
"False". The other 2 conditions work fine.
I think I might know what the issue is. To make sure
that
the 9th character
is uppercase, I am using the following to convert it -
=PROPER(A1)
This is obviously changing it to Text. How do I get
around this?
Thanks
Ruan
"Biff" wrote in message
.. .
Hi!
...doesn't seem to work for option 1, which is a
straight
10 digit numeric ID
It should work. That's the easiest condition to test
for!
It works in my test file. Are you sure the 10 char
string
is in fact a numeric value and not TEXT?
Biff
-----Original Message-----
Hello Biff,
The alpha character is upper case. The formula works
great for the valid
options 2 and 3, but doesn't seem to work for option
1,
which is a straight
10 digit numeric ID.
1) All numeric 10 character ID (example: 1234567890)
2) Alphanumeric 10 character ID, with the 9th
character
being a Alpha
character (example: 12345678E0)
3) Alphanumeric 14 character ID, with the 9th
character
being a Alpha
character (example: 12345678A01234)
Thanks so much for your help.
Ruan
"Biff" wrote in message
.. .
Hi!
Does it matter if the alpha char is upper or lower
case? I
see in your examples they're upper case.
This will work for upper case:
=OR(AND(ISNUMBER(A1),LEN(A1)=10),AND(LEN(A1)=10,CO DE
(MID
(A1,9,1))=65,CODE(MID(A1,9,1))<=90,ISNUMBER(--
SUBSTITUTE
(A1,MID(A1,9,1),""))),AND(LEN(A1)=14,CODE(MID
(A1,9,1))
=65,CODE(MID(A1,9,1))<=90,ISNUMBER(--SUBSTITUTE
(A1,MID
(A1,9,1),""))))
If you want to accept lower case, then just change
the
CODE values to =97 and <=122.
If you want either upper or lower, drop back and
punt!
You could use the CODE range from =65 and <=122 but
that
would leave open the possibilty that CHARS 91
through
96
could be entered.
Biff
-----Original Message-----
Hello,
I am struggling to write a formula to validate an ID
Number.
Here are the valid options -
1) All numeric 10 character ID (example:
1234567890)
2) Alphanumeric 10 character ID, with the 9th
character
being a Alpha
character (example: 12345678E0)
3) Alphanumeric 14 character ID, with the 9th
character
being a Alpha
character (example: 12345678A01234)
The following examples have been used, but are not
acceptable -
1) 1234567*9
2) 1234567*901234
3) 12345678'9
4) 12345678,9
Any help will be greatly appreciated.
Thanks
Ruan
.
.
.
.
|