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

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

Hi,
it seems that array formula cannot work well in Data-Validation.
i suppose UDF is better than a long logn formula.
i don't know any good way to use a UDF in Data-Varidation,
but the following seems to work on my PC. (excel 2000)

1. make a UDF in a standard module like this:

'Module1
Function CheckFormat(Cell1 As Range, Cell2 As Range) As Range
Dim s As String
On Error GoTo ErrorHandler
Set CheckFormat = Cell1
s = UCase(Cell1.Value)
If s Like "A-TFG-####*" Or s Like "B-TFG-####*" Then
s = Mid(s, 11)
If s = "" Or s Like "[A-Z]" Or s Like "[A-Z][A-Z]" Then
Exit Function
End If
ElseIf s Like "[A-Z][A-Z][A-Z]####" And (Not s Like "TFG*") Then
Exit Function
End If
ErrorHandler:
Set CheckFormat = Cell2
End Function


2. select C10 and do Insert Name Define
Names in workbook:
Check1
Refers to:
=CheckFormat(!C10,!$G$1)

3. put =NA() in G1. (used as a working area)

4. select C10 and do Data Validation Settings
Allow:
Custom
Formula:
=Check1=C10

or

Allow:
List
Source:
=Check1

[ ] In-cell dropdown(off)

--
HTH,

okaizawa


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

What array formula were you refering to?

--
Regards,
Tom Ogilvy

"okaizawa" wrote in message
...
Hi,
it seems that array formula cannot work well in Data-Validation.
i suppose UDF is better than a long logn formula.
i don't know any good way to use a UDF in Data-Varidation,
but the following seems to work on my PC. (excel 2000)

1. make a UDF in a standard module like this:

'Module1
Function CheckFormat(Cell1 As Range, Cell2 As Range) As Range
Dim s As String
On Error GoTo ErrorHandler
Set CheckFormat = Cell1
s = UCase(Cell1.Value)
If s Like "A-TFG-####*" Or s Like "B-TFG-####*" Then
s = Mid(s, 11)
If s = "" Or s Like "[A-Z]" Or s Like "[A-Z][A-Z]" Then
Exit Function
End If
ElseIf s Like "[A-Z][A-Z][A-Z]####" And (Not s Like "TFG*") Then
Exit Function
End If
ErrorHandler:
Set CheckFormat = Cell2
End Function


2. select C10 and do Insert Name Define
Names in workbook:
Check1
Refers to:
=CheckFormat(!C10,!$G$1)

3. put =NA() in G1. (used as a working area)

4. select C10 and do Data Validation Settings
Allow:
Custom
Formula:
=Check1=C10

or

Allow:
List
Source:
=Check1

[ ] In-cell dropdown(off)

--
HTH,

okaizawa


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(ISNUMBER(MID(C10,11,1)*1)) )),(AND(ISNUMBER(RIGHT(C10
,4)*1),NOT(ISNUMBER(LEFT(C10,1)*1)),NOT(ISNUMBER(M ID(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

Tom Ogilvy wrote:
What array formula were you refering to?

sorry for my insufficient and strange explanation.
it is my array formula that has not worked in Data-Validation
like this:

=OR(LEFT(C10,6)={"A-TFG-","B-TFG-"})*AND(ISNUMBER(--MID(C10,{7,8,9,10},1)))
*AND(ABS(CODE(UPPER(MID(C10,{11,12},1)&"a"))-77.5)<13)*(LEN(C10)<=12)
+AND(ABS(CODE(UPPER(MID(C10,{1,2,3},1)))-77.5)<13)*(LEFT(C10,3)<"TFG")
*AND(ISNUMBER(--MID(C10,{4,5,6,7},1)))*(LEN(C10)=7)

or a little bit shorter

=OR(LEFT(C10,6)={"A-TFG-","B-TFG-"})*AND(ISNUMBER(--MID(C10,{7,8,9,10},1))
,ABS(CODE(UPPER(MID(C10,{11,12},1)&"a"))-77.5)<13,LEN(C10)<13)
+AND(ABS(CODE(UPPER(MID(C10,{1,2,3},1)))-77.5)<13,LEFT(C10,3)<"TFG"
,ISNUMBER(--MID(C10,{4,5,6,7},1)),LEN(C10)=7)

i think that it is necessary to test characters one by one.
without array, a long formula will be needed.

=((LEFT(C10,6)="A-TFG-")+(LEFT(C10,6)="B-TFG-"))
*ISNUMBER(--MID(C10,7,1))*ISNUMBER(--MID(C10,8,1))
*ISNUMBER(--MID(C10,9,1))*ISNUMBER(--MID(C10,10,1))
*(ABS(CODE(UPPER(MID(C10,11,1)&"a"))-77.5)<13)
*(ABS(CODE(UPPER(MID(C10,12,1)&"a"))-77.5)<13)
*(LEN(C10)<=12)
+(ABS(CODE(UPPER(MID(C10,1,1)))-77.5)<13)
*(ABS(CODE(UPPER(MID(C10,2,1)))-77.5)<13)
*(ABS(CODE(UPPER(MID(C10,3,1)))-77.5)<13)
*(LEFT(C10,3)<"TFG")
*ISNUMBER(--MID(C10,4,1))*ISNUMBER(--MID(C10,5,1))
*ISNUMBER(--MID(C10,6,1))*ISNUMBER(--MID(C10,7,1))
*(LEN(C10)=7)

being divided into multiple names, this will work in Data-Validation
instead of a UDF. but it is more difficult and time$B!(Bconsuming.

--
Regards,

okaizawa

Thanks a million to all.