200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Hi,
Below are the conditions :
1) cell should contain exactly 10 characters
2)should start with either A or B or C or D only,
3) first 5 characters to be letters,
4) 6th, 7th, 8th 9th characters should be a number,
5) last character should be a letter
If possible,
6) all letters should be in caps,
Thanks in advance :-)
You need a macro. This will work:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Target, Range("A1:A5")) Is Nothing Then
char_count = Len(Target.Value)
first_letter = Left(Target.Value, 1)
second_letter = Mid(Target.Value, 2, 1)
third_letter = Mid(Target.Value, 3, 1)
fourth_letter = Mid(Target.Value, 4, 1)
fifth_letter = Mid(Target.Value, 5, 1)
six_to_nine = Mid(Target.Value, 6, 4)
last_char = Right(Target.Value, 1)
'letter count
If char_count <> 10 And Target.Value <> "" Then
MsgBox "Error"
Target.Value = ""
End If
'first letter value
If UCase(first_letter) <> "A" And UCase(first_letter) <> "B" And UCase(first_letter) <> "C" And UCase(first_letter) <> "D" And Target.Value <> "" Then
MsgBox "Error"
Target.Value = ""
End If
'character value
If IsNumeric(last_char) Or IsNumeric(second_letter) Or IsNumeric(third_letter) Or IsNumeric(fourth_letter) Or IsNumeric(fifth_letter) And Target.Value <> "" Then
MsgBox "Error"
Target.Value = ""
End If
'numbers
If Not (IsNumeric(six_to_nine)) And Target.Value <> "" Then
MsgBox "Error"
Target.Value = ""
End If
'make upper case
Target.Value = UCase(Target.Value)
End If
Application.EnableEvents = True
End Sub
Install this code in the worksheet where you need it to run.
Change A1:A5 to the range of cells on which you want it to run.
Change "Error" to the desired error message.