Validation rule

[isethjmave]

Hi,

Need your help. I'm trying to create a validation rule for a textfield that will have the following pattern [Number Except 0][T]. I tried this rule "*[0-9][T]" the problem with this is that it will accept 0T. But when i tried to changed it to "*[1-9][T]" the problem is that if you enter 10T it gives you error.

Any suggestion on what to do?

Thanks

 

[vbaInet]

Show us examples of entries that should pass and fail, and explain why.

 

[isethjmave]

Valid entries will be between [1 to 100][T] Like 1T, 10T, 20T. Not accepted 0T, 01T, 1A, 1TB, any special characters before T like %T and any character after T like 1Te are not accepted.

Numbers entered corresponds to column numbers, the letter corresponds to a certain code but it should only be one letter. There are 4 letters that will be allowed it could be either T, B, L or R.

Thanks

 

[vbaInet]

So the rule is as follows:

1. 4 characters max (i.e. three digits, one letter)
2. 2 character min required (i.e. one digit, one letter)
3. First three characters must be digits and last must be a letter (i.e. T, B, L or R)
4. Digits must be in the range of 1 to 100
5. Cannot begin with 0

Correct me if I'm wrong or add to the rule.

 

[isethjmave]

hi vbaInet,

That's the rules. Thanks.

 

[vbaInet]

Alright, the LIKE operator won't cut it in this case. It would have to be Regex in VBScript. I'll write it up when I have a min.

 

[vbaInet]

Here's the function you can use:

Public Function ValidText(varInput As Variant) As Boolean
    Dim vbRegEx As Object
    
    If nz(varInput, vbNullString) = vbNullString Then
        Exit Function
    End If
    
    Set vbRegEx = CreateObject("vbscript.regexp")
    
    With vbRegEx
        .IgnoreCase = True
        .Pattern = "^[1-9]{1,3}[TBLR]$"
        ValidText = .Test(varInput)
    End With
    
    Set vbRegEx = Nothing
End Function

Comment out the IgnoreCase line if you want it to be case sensitive on the letters.

 

[isethjmave]

Thanks vbaInet the code works fine. The problem i encounter when i enter like 10T it returns false which should be true. When i change the pattern [1-9] to [0-9] then it will accept 0T which it shouldnt. Any suggestion on what to do?

Thanks very much

 

[isethjmave]

hi vbaInet i change the pattern to ^[1-9][0-9]{1,3}[TBLR]$ and it works perfectly now. thanks very much

 

[vbaInet]

Alright, I missed that. Let's try this one:

"^[1-9]([0-9]|){1,2}[TBLR]$"

 

[vbaInet]

Ah I just saw your response. I think it will fail when you input 1T.

 

[isethjmave]

Thanks very much. code works perfectly now.

 

[vbaInet]

Pleased to hear.