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
2
-
Do you mean, how do you get another column to only show those values which meet your criteria? Are you looking for range matching or exact matching? Can you give some examples of values you want to match, and some values that are close but shouldn't match?jimbo– jimbo2013-01-16 21:05:26 +00:00Commented Jan 16, 2013 at 21:05
-
1Would like the value to be one of COM1, COM2, COM3, COM4 or an int 1-65535fred basset– fred basset2013-01-16 21:20:00 +00:00Commented Jan 16, 2013 at 21:20
Add a comment
|
4 Answers
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.
1 Comment
brettdj
+1 RegExp is overkill for what can be accomplished without VBA
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
4 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.
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.
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
For this specific question, using standard Data Validation would have worked with no VBA, Regex or formulas needed:
Allow: List Source: "COM1,COM2,COM3,COM4,1-65535"
This also provides a dropdown list in the cell to help user.
1 Comment
ADJenks
The list would need to contain the numbers 1 through 65535...