AlphaNumeric value

[sujittalukde]

In a cell say B2 the value to be entered is say AACVF2001G
Total length is 10
First 5 letters will be alphabets
Next four letters will be Numeric
Last letter will be Alphabet

How can a condition be set so that if a user enters a value wrongly ie not in the order, excel will not accept that value.

 

[jindon]

vba
how about

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0,0) <> "B2" Then Exit Sub
If Not Target.Value Like "?????####?" Then
     MsgBox "Invalid Entry"
     Target.Activate
End If
End Sub

 

[Stormseed]

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0,0) <> "B2" Then Exit Sub
If Not .Value Like "?????####?" Then
MsgBox "Invalid Entry"
Target.Activate
End If
End Sub

thank you, sir. This was great !

 

[pgc01]

Hi sujittalukde

Jindon's code is perfect if you want to use vba.

You can also use Data Validation with a custom condition:

=AND(ABS(CODE(UPPER(MID(A1,ROW($1:$6)+4*(ROW($1:$6)=6),1)))-77.5)<=12.5,ISNUMBER(-MID(A1,ROW($6:$9),1)))

Hope this helps
PGC

 

[sujittalukde]

Hi PGC your formula works greatly.

Dear jindon, your code is also great but allow users to input for 4alpha5numeric&1alpha.

Actually my need is that out of 10 digits, first 5 will always be alpha,next4 as numeric and next 1 as alpha.
anyway thanks to all.