Data Validation Formula - End in 7 Numbers

[tomexcel1]

Hi All

I've been trying to rack my brain for ages just cant work out how to use a formula to force user to end entry with 7 numbers. (Case of the letters is not important)

For example these should be ok:

Hill J 1234567
Joe R 1345647
Smith 9864567

These should fail:

Hill J
Smith
Joe R 123

Thanks in Advance
Tom

 

[Scott Huish]

In this example, I'm using Column A, change as necessary.

Data Validation>Custom
=ISNUMBER(RIGHT(A1,7)+0)

 

[jtakw]

Hi,

Try this:

=RIGHT(A1,7)+0

 

[tomexcel1]

Thank you so much! Works great! Knew it would be something simple

 

[Scott T]

In this example, I'm using Column A, change as necessary.

Data Validation>Custom
=ISNUMBER(RIGHT(A1,7)+0)

That was my first thought but if there is a space before a 6 digit number it still returned TRUE

try

=AND(ISNUMBER(--RIGHT(A1,7)),LEN(SUBSTITUTE(RIGHT(A1,7)," ",""))=7)

Now that I think about there is nothing stopping the user from entering more then 7 digits. If that is a problem then try

=AND(ISNUMBER(--RIGHT(A1,7)),LEN(SUBSTITUTE(RIGHT(A1,7)," ",""))=7,NOT(ISNUMBER(--LEFT(RIGHT(A1,8),1))))

 

[tomexcel1]

Thanks Scott T! Dint even think about that. Does just what I need it to do

Thanks all
Tom

 

[jtakw]

For the 2 scenarios presented by Scott:

6 numbers with a space in front, use:

=LEN(RIGHT(A1,7)+0)=7

6 numbers with a space And/or more than 7 numbers, use:

=AND(LEN(RIGHT(A1,7)+0)=7,LEN(RIGHT(A1,8)+0)<>8)

A little simpler.

BTW, The ISNUMBER test is Not needed (like my post # 3), any number evaluates to TRUE, any Error evaluates to FALSE.