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

Solution 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.

Solution 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

Solution 3

Use the below in Ribbon >> Data >> Data Validation. Then Allow = Custom and add the below in the Formula:

=OR(AND(E5 >= 1, E5 <= 65535), E5 = "COM1", E5 = "COM2", E5 = "COM3", E5 = "COM4")

Comments

[fred basset]

If you could show how to do it in a macro that would be great.

[Jon Lawton]

I should be more specific. I've created functions. I don't care much for Macros (they have their place), but you'll still need to use a lot of the same custom VBA code. I'll attach the code above.

[brettdj]

+1 RegExp is overkill for what can be accomplished without VBA

[The Thirsty Ape]

Perfect! Not sure why everyone else suggests regular excel functions whenever regex is suggested

[Jon Lawton]

@Foo_Chow if you can easily avoid using RegEx in Excel, do so; it will run faster without it. However, when something is dynamic enough that it requires regular expressions, it is a lot better than a list of raw text.

[ADJenks]

The list would need to contain the numbers 1 through 65535...