AlphaNumeric value

sujittalukde

Well-known Member
Joined
Jun 2, 2007
Messages
520
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
Joined
Aug 21, 2004
Messages
16,995
vba
how about
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
Joined
Sep 18, 2006
Messages
3,274
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
Joined
Apr 25, 2006
Messages
19,887
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
Joined
Jun 2, 2007
Messages
520
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.
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top