Hi,
Try this UDF and change function return value to suit your needs

Sub test()
MsgBox DataVal("A-TFG-123ab")
End Sub

or

A1="abc1234"
B!= "=dataval(A1)"

Function DataVal(indata)
Dim strdata As String, i As Integer, valid As Boolean

strdata = indata
valid = True

Select Case Left(strdata, 6)

Case Is = "A-TFG-", "B-TFG-"
If Len(strdata) = 10 Then
For i = 7 To 10 ' Check 0 to 9
If Asc(UCase(Mid(strdata, i, 1))) < 48 Or Asc(UCase(Mid(strdata, i,
1))) 57 Then
valid = False
End If
Next i
If Len(strdata) 10 And Len(strdata) <= 12 Then
For i = 11 To Len(strdata) ' Check A to Z
If Asc(UCase(Mid(strdata, i, 1))) < 65 Or
Asc(UCase(Mid(strdata, i, 1))) 90 Then
valid = False
End If
Next i
Else
valid = False
End If
Else
valid = False
End If

Case Else

If Len(strdata) = 7 Then
For i = 1 To 3 ' Check A to Z
If Asc(UCase(Mid(strdata, i, 1))) < 65 Or Asc(UCase(Mid(strdata, i,
1))) 90 Then
valid = False
End If
Next i
For i = 4 To 7 ' Check 0 to 9
If Asc(UCase(Mid(strdata, i, 1))) < 48 Or Asc(UCase(Mid(strdata, i,
1))) 57 Then
valid = False
End If
Next i
If UCase(Left(strdata, 3)) = "TFG" Then
valid = False
End If

Else
valid = False
End If

End Select
If Not valid Then
DataVal = "Invalid data"
Else
DataVal = "OK"
End If
End Function


HTH

"broogle" wrote:

Hi, I'm having dificulties fitting the formula in data validation.

The condition should be :

1. A-TFG-1234(a or ab)
2. B-TFG-1234(a or ab)
or
3. ABC1234 - (TFG1234 is not allowed)

Note:

For No.1 and No. 2 :
"A-TFG-" or " "B-TFG-" is mandatory.
1234 is numeric only
(a or ab) any alphabets (lowercase or uppercase are allowed)
minimum 10 character, maximum 12 character
sample : A-TFG-4675, B-TFG-6544KL or B-TFG2344G

For No. 3
ABC is (a|A..z|Z only) but TFG is not allowed
1234 is a number
max 7 character
sample : BTU3456, wrt2231
sample : TFG2245 (is not allowed)

Below is my code:

=OR(AND(OR(LEFT(C10,6)="A-TFG-",LEFT(C10,6)="B-TFG-"),ISNUMBER(MID(C10,7,4)*1),AND(LEN(C10)=10,NOT(I SNUMBER(MID(C10,11,1)*1)))),(AND(ISNUMBER(RIGHT(C1 0,4)*1),NOT(ISNUMBER(LEFT(C10,1)*1)),NOT(ISNUMBER( MID(C10,3,1)*1)),LEN(C10)=7,LEFT(C10,3)<"TFG")))

still not cover everything due to the lack of space.

Anybody can simplify this code?
Please help.

Thank you