In the VBA editor, create a new Module in your workbook (you'll need to save it as .xls or .xlsm, not .xlsx) and paste code like this:
Function RegEx(Pattern As String, TextToSearch As String) As String 'vv Dim RE As Object, REMatches As Object Set RE = CreateObject("vbscript.regexp") With RE .MultiLine = False .Global = False .IgnoreCase = False .Pattern = Pattern End With Set REMatches = RE.Execute(TextToSearch) If REMatches.Count > 0 Then RegEx = REMatches(0) Else RegEx = vbNullString End If End Function '^^ Public Function IsGood(stir As String) If RegEx("[0-9][0-9]\.[0-9][0-9]/[0-9][0-9]\.[0-9][0-9]", stir) = stir Then IsGood = "GOOD" Else IsGood = "BAD" End If End Function The first function, RegEx, performs a regular expression match. The second function, IsGood, returns the string GOOD if the output string matches the input string (i.e. the whole string matched exactly with the regex), and BAD otherwise.
Now, in a spare column somewhere (it can be a hidden column) in your worksheet, write a formula like:
=IsGood(A1) (using anchors as appropriate if you need to do this for multiple rows)
Then go to Data -> Data Validation -> Data Validation... on the Ribbon, with the cells containing the data to test selected in the worksheet.
Change the "Allow:" dropdown box to say "Custom", and the "Formula" to say, for example:
=B1="GOOD"
Replace "B1" with the reference to the first cell containing your =IsGood formula -- not the cells containing your input data.
Now, to test your results:
- Enter something like "hello" into a cell subject to data validation, and you'll get an error message and won't be allowed to make the edit.
- Enter something like "12.34/56.78" and it'll work fine, just as normal in Excel.
- Enter something like "12.34,56.78" and you'll get the data validation error because of the comma not matching the regular expression.
You can tweak the regex in my code to be more lax if you can accept values like "12" instead of "12.00", but that would depend on your exact requirements and your ability to comprehend regular expressions.
Basically, [0-9][0-9]\.[0-9][0-9]/[0-9][0-9]\.[0-9][0-9] is a regular expression that says "match a 2-digit number followed by a period, then another 2-digit number, then a forward slash, then another 2-digit number, then a period, then another 2-digit number". This is fairly strict:
- Any spaces will fail to match
- If any of the numbers contain 0, 1, or 3 or more digits, it'll fail to match. For example,
12.375/37.00would fail because of the extra digit,5. - Using a backslash
\instead of a forward slash/would fail to match. You can use the alternation character|in the regex to indicate that either of two possibilities are acceptable, and in this way you could allow EITHER a forward OR a back slash. - The possibilities go on. You can be more lax and allow any number of digits using quantifiers. I'm not going to cover all the possibilities of regular expressions in this answer.