Cell validation = format?

[John]

Hi

I want to control the format of data entered into a cell to

LNNNN LNNNN

where L = Letter & N = Number (with a space between the two sets)

Anyone any ideas how I can do this?

Thanks

John

 

[Barb Reinhardt]

Use this as a worksheet change event to test the cells. I didn't get to
doing anything for the alphanumeric values and need to go, but it's a start

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count > 1 Then Exit Sub

If Not Intersect(Target, Me.Range("A1:A100")) Is Nothing Then
If Len(Target.Text) <> 11 Then
MsgBox ("Enter a value in the cell in format ANNN ANNN")
End If
For i = 1 To Len(Target.Text)
mystring = Mid(Target.Text, i, 1)
Debug.Print mystring
If i = 1 Or i = 7 Then

ElseIf i = 6 Then
If Not mystring = " " Then
MsgBox ("The value in the 5th position must be a blank")
Exit Sub
End If
Else
If Not IsNumeric(mystring) Then
MsgBox ("The value in position " & i & " must be numeric")
Exit Sub
End If
End If

Next i


End If

End Sub

 

[T. Valko]

This seems to work based on the following conditions:

Total length must be 11 characters
6th character must be a space
1st character must be an uppercase letter A-Z
7th character must be an uppercase letter A-Z
Characters 2-5 and 8-11 must be numbers

=AND(COUNTIF(A1,"????? ?????"),
CODE(LEFT(A1))>=65,CODE(LEFT(A1))<=90,
CODE(MID(A1,7,1))>=65,CODE(MID(A1,7,1))<=90,
COUNT(-MID(A1,ROW(INDIRECT("2:11")),1))=8)