Data Validation

[Guest]

I'm having some difficulty on my data validation...

I need to validate that a number entered into a cell is:
9 characters wide
9 numbers (123456789, 012345678, etc.)
OR
Starts with an E and has 8 numbers after it (E12345678, E01234567, etc.)

Here's my validation formula:
=OR(AND(TYPE(A2)=2,LEFT(A2,1)="e",TYPE(RIGHT(A2,8)=1),LEN(A2)=9),AND(TYPE(A2)=1,LEN(A2)>=7,LEN(A2)<=9))

Now, from what I've read here on the board, this should be working if all of
these are true:
TYPE(A2)=2 'cell is text
LEFT(A2,1)="e" 'starts with an e
TYPE(RIGHT(A2,8)=1) 'ends in 0 numbers
LEN(A2)=9 'total length is 9 characters
OR
TYPE(A2)=1 'entire value is a number
LEN(A2)>=7 'cell has at least 7 digits
LEN(A2)<=9 'cell has no more than 9 digits
'these last two lines are for SSN's that can begin with 00. There should be
no SSN's that start with 000.

For some reason, I can enter e1a345678 and it takes that value, not picking
up that while it starts with an e, it doesn't have 8 numbers following it.
It DOES however pick up a12345678 as an error since it doesn't start with an
e.

I did some troubleshooting...putting the right 8 characters of a2 in a cell
(=RIGHT(A2,8)) and when I try to 'type' that cell, it comes back as text
instead of numbers, even though it contains '12345678'. Any idea why this is?

THX guys!

 

[JE McGimpsey]

one way:

=IF(ISNUMBER(--A2) ,AND(--A2>=1000000, --A2<=999999999),
AND(LEFT(A2,1)="E", LEN(A2)=9, ISNUMBER(--MID(A2,2,8))))

 

[Harlan Grove]

I need to validate that a number entered into a cell is:
9 characters wide
9 numbers (123456789, 012345678, etc.)
OR
Starts with an E and has 8 numbers after it (E12345678, E01234567,
etc.)

....

Unless the cell has the Number format Text, typing 012345678 will
result in 12345678. That is, any leading zeros will be stripped off.
If you'd accept any numeric entry with no more than 9 decimal digits
or E followed by 8 decimal digits, then try

=COUNT(1/(LEN(TEXT(A2,"000000000"))=9),1/(LEFT(A2,1)="E")/
(INT(-RIGHT(A2,8))=-RIGHT(A2,8))/(ABS(50000000-RIGHT(A2,8))<50000000),
1/(INT(A2)=A2)/(ABS(500499999.5-A2)<499500000))=2

 

[Guest]

This worked...but cna you explain how? I don't understand what the double
dashes do...

 

[JE McGimpsey]

Unary minus (-) converts inverts the sign of a number. Since it's a math
operator, it will also cause text values that can be interpreted as
numbers to true numbers.

The second unary minus restores the sign.