Data Validation
 

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!

Data Validation
 

one way:

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

In article ,
Robert_L_Ross wrote:

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!

Data Validation
 

Robert_L_Ross wrote...
....
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

Data Validation
 

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

"JE McGimpsey" wrote:

one way:

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

In article ,
Robert_L_Ross wrote:

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!

Data Validation
 

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.

In article ,
Robert_L_Ross wrote:

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