Excel data validation - regular expressions?

Question

I have a column in an Excel 2007 spreadsheet where I want the data to be restricted to values "COM[1-4] or 1-65535". How can I get Excel to enforce this set of values?

TY, Fred

Answer 1

Not very flexible, but you could use a Custom Data Validation, with this formula:

=OR(AND(LEN(A2)=4,LEFT(A2,3)="COM",MID(A2,4,1)>="1",MID(A2,4,1)<="4"),OR(AND(A2>=1,A2<=65535)))

I think this narrows it down to what you've specified in your comment.

Answer 2

You will need to create a custom VBA function (or Macro) that supports Regular Expressions. I've personally done this, so I know it's possible :-)

If this is a one-time issue, it can be solved by using a complex FIND()/MID()/SUBSTITUTE(), etc. (this is my day job, actually), but I wouldn't recommend this unless you want to stare at a possible 5 line cell full of Excel functions.

Edit: I will update this if you have or need further info to offer.

Edit: Here's how you can use regular expressions as a function in Excel:

Function REFIND(strValue As String, strPattern As String, Optional blnCase As Boolean = True) As Boolean

    Dim objRegEx As Object
    Dim blnValue As Boolean

    ' Create regular expression.
    Set objRegEx = CreateObject("VBScript.RegExp")
    objRegEx.Pattern = strPattern
    objRegEx.IgnoreCase = blnCase

    ' Do the search test.
    blnValue = objRegEx.Test(strValue)

    REFIND = blnValue End Function
End Function