cell contents - 10 digits or greater

[ajm]

I am trying to force my users into giving me data in a certain format. this particular cell needs to be atleast 10 numbers long, or if its greater than 10, the eleventh must be "/" followed by a last single numeric. for example: 1234567891 or 1234567899/1

 

[T. Valko]

I am trying to force my users into giving me data in a certain format. this particular cell needs to be atleast 10 numbers long, or if its greater than 10, the eleventh must be "/" followed by a last single numeric. for example: 1234567891 or 1234567899/1

Can the digit string start with leading zeros?

0004567890

0004567890/0

 

[ajm]

i guess it could. these 10 digit numbers are for consignment notes (CN) which start at 4000000000. i don't think there will ever be an instance where a CN would start with zero though.

 

[T. Valko]

i guess it could. these 10 digit numbers are for consignment notes (CN) which start at 4000000000. i don't think there will ever be an instance where a CN would start with zero though.

Try this...

Apply Data Validation

Let's assume you want to apply this rule to cell A1.

Select cell A1
Data>Validation
Allow: Custom
Formula:

=OR(AND(LEN(A1)=10,A1>=4000000000,A1<=9999999999),AND(LEN(A1)=12,--LEFT(A1,10)>=4000000000,--LEFT(A1,10)<=9999999999,MID(A1,11,1)="/",COUNT(-RIGHT(A1))))

 

[ajm]

thanks biff. something is awry with the validation as it keeps coming up with the error message that the value is not valid. it has something to do with the "mid" part of the formula and I will play with it from there until it works. many thanks for the kick start.

 

[T. Valko]

thanks biff. something is awry with the validation as it keeps coming up with the error message that the value is not valid. it has something to do with the "mid" part of the formula and I will play with it from there until it works. many thanks for the kick start.

Here's what the formula allows:

Length must be 10 characters
The number must be >=4000000000 and <=9999999999

OR

Length must be 12 characters
The 1st 10 characters must be a number >=4000000000 and <=9999999999
The 11th character must be a slash: /
The 12th character must be a number from 0 to 9

 

[mikerickson]

Isn't "Length must be 10 characters" redundant if "The number must be >=4000000000 and <=9999999999"

also, try this for the third condition of the second OR
COUNT(-RIGHT(A1))=1

 

[ajm]

yes. length =10 is redundant. and with the change you suggest mike and with biff's original custom formula, i get the value entered is not valid error message when entering anything in that cell. the first condition works, that is that the number must be bewteen 4000000000 and 9999999999, so it must be due to second condition. I am playing around with that second one now and will post if i can make it work.

 

[T. Valko]

Isn't "Length must be 10 characters" redundant if "The number must be >=4000000000 and <=9999999999"

Yeah, I guess it is.

also, try this for the third condition of the second OR
COUNT(-RIGHT(A1))=1

Why?

Isn't that redundant?

COUNT(-RIGHT(A1))

Will return either 1 or 0.

If it returns 1 then AND evaluates that as TRUE. If it returns 0 then AND evaluates it as FALSE.

 

[T. Valko]

Here's what the formula allows:

Length must be 10 characters
The number must be >=4000000000 and <=9999999999

OR

Length must be 12 characters
The 1st 10 characters must be a number >=4000000000 and <=9999999999
The 11th character must be a slash: /
The 12th character must be a number from 0 to 9

yes. length =10 is redundant. and with the change you suggest mike and with biff's original custom formula, i get the value entered is not valid error message when entering anything in that cell. the first condition works, that is that the number must be bewteen 4000000000 and 9999999999, so it must be due to second condition. I am playing around with that second one now and will post if i can make it work.

Are the "rules" I summarized in that other post correct?