AlphaNumeric value

sujittalukde

Well-known Member
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.

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

jindon

MrExcel MVP
vba
Code:
``````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

Banned
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

MrExcel MVP
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

Well-known Member
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.

Replies
7
Views
543
Replies
0
Views
241
Replies
8
Views
186
Replies
0
Views
87
Replies
6
Views
140

1,181,056
Messages
5,927,864
Members
436,573
Latest member
CMR237

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back