Data validation

0

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 :-) 

Answer
Discuss

Answers

0

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.

Discuss

Answer the Question

You must create an account to use the forum. Create an Account or Login