Validation formula

[sujittalukde]

I want a validation formula for the following:

The text structure is as given : AAVCF5678G

The Length is 10
Left 5 are characters from A to Z
Next four are Numbers from 0 to 9
Last one is Character.

How do I design validation formula so that user can be prompted if the input structure is not valid?

 

[Domski]

Maybe:

=AND(AND(CODE(LEFT(A1,1))>=65,CODE(LEFT(A1,1))<=90),AND(CODE(MID(A1,2,1))>=65,CODE(MID(A1,2,1))<=90),AND(CODE(MID(A1,3,1))>=65,CODE(MID(A1,3,1))<=90),AND(CODE(MID(A1,4,1))>=65,CODE(MID(A1,4,1))<=90),AND(CODE(MID(A1,5,1))>=65,CODE(MID(A1,5,1))<=90),ISNUMBER(MID(A1,6,4)*1),AND(CODE(RIGHT(A1,1))>=65,CODE(RIGHT(A1,1))<=90))

Dom

 

[Domski]

Note that you won't be able to use that as a custom validation formula directly as it's longer than the 255 character limit.

You can put the formula in a spare cell and refer to that as the source of the validation check.

Dom

 

[sujittalukde]

Thanks for the reply.
But the formula gives TRUE if the LEN is greater than 10 due to mid Numbers only ie if the numbers are 5 and thus LEN is coming to 11, it gives TRUE result.
Like, AAVCF56789G will give TRUE which is wrong.

 

[Domski]

Try this:

=AND(AND(CODE(LEFT(A1,1))>=65,CODE(LEFT(A1,1))<=90),AND(CODE(MID(A1,2,1))>=65,CODE(MID(A1,2,1))<=90),AND(CODE(MID(A1,3,1))>=65,CODE(MID(A1,3,1))<=90),AND(CODE(MID(A1,4,1))>=65,CODE(MID(A1,4,1))<=90),AND(CODE(MID(A1,5,1))>=65,CODE(MID(A1,5,1))<=90),ISNUMBER(MID(A1,6,4)*1),AND(CODE(RIGHT(A1,1))>=65,CODE(RIGHT(A1,1))<=90),LEN(A1)=10)

Dom

 

[sujittalukde]

Thanks, its working now correctly.
Can a VBA function be provided ?

 

[Domski]

Other than replicating the above you could probably do something using regular expressions but I'm fairly clueless when it comes to them. As it can be done without why would you want to?

Dom

 

[T. Valko]

I want a validation formula for the following:

The text structure is as given : AAVCF5678G

The Length is 10
Left 5 are characters from A to Z
Next four are Numbers from 0 to 9
Last one is Character.

How do I design validation formula so that user can be prompted if the input structure is not valid?

Try this...

Create these defined names:

• Array1
• Refers to: ={1,2,3,4,5,6}
• Array2
• Refers to: ={6,7,8,9}
• Letters
• Refers to: ="ABCDEFGHIJKLMNOPQRSTUVWXYZ"

Then, use this data validation formula:

=AND(LEN(A1)=10,FIND(MID(LEFT(A1,2)&RIGHT(A1),Array1,1),Letters),ISNUMBER(-MID(A1,Array2,1)))

Note that if you test that formula on the worksheet it must be array entered**.

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

 

[T. Valko]

Try this...


Create these defined names:

• Array1
• Refers to: ={1,2,3,4,5,6}
• Array2
• Refers to: ={6,7,8,9}
• Letters
• Refers to: ="ABCDEFGHIJKLMNOPQRSTUVWXYZ"

Then, use this data validation formula:

=AND(LEN(A1)=10,FIND(MID(LEFT(A1,2)&RIGHT(A1),Array1,1),Letters),ISNUMBER(-MID(A1,Array2,1)))

Note that if you test that formula on the worksheet it must be array entered**.

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

I forgot to mention...

If using that method, when setting the validation uncheck: Ignore blank.

 

[sujittalukde]

Thanks to duo for the help.