How do I check if a cell only contains characters from a specific list?

Question

I'm trying to do a check on a data column to see if its rows only contain specific characters. The characters in question are 0-9 and /. If only those are present, an OK should be returned, otherwise an error (or 1 and 0, anything distinguishable is fine). It should basically look like this (bold column):

I had limited success with MATCH and VLOOKUP but nothing that employs a range. A formula would be preferred but VBA's cool too. Any ideas?

Please don't just ask us to solve the problem for you. Show us how you tried to solve the problem yourself, then show us exactly what the result was, and tell us why you feel it didn't work. See also the help pages (linked at the top of every page) for how to write a good question. — AdrianHHH, Mar 17 '15 at 22:39
Probably overkill, but one way is to use [VBA Regex](http://stackoverflow.com/q/22542834/2521004) — Automate This, Mar 17 '15 at 22:51

pnuts · Answer 1 · 2015-03-17T23:19:30.250

3

With a formula (in B1 and copied down if 12 is in A1):

=IF(ISNUMBER(VALUE(SUBSTITUTE(A1,"/",""))),"OK","ERROR")

Adding in exclusion of . makes it a bit longer:

=IF(AND(ISERROR(FIND(".",A1)),ISNUMBER(VALUE(SUBSTITUTE(A1,"/","")))),"OK","ERROR")

edited Mar 17 '15 at 23:19

answered Mar 17 '15 at 23:00

pnuts

58,317
11
87
139

score 1 · Answer 2 · answered Mar 18 '15 at 01:54

For a formula approach, the following array-entered formula will return TRUE or FALSE depending on the presence of non-allowed characters. To confirm the formula, hold down ctrl-shift while hitting enter and Excel will place braces {...} around the formula.

=AND(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))>=47,CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<=57)

You can use this as logical_test in an IF function to return whatever you want.

score 0 · Accepted Answer · answered Mar 17 '15 at 22:52

0

Give this UDF() a try:

Public Function CellTest(sIn As Variant) As String
    Dim L As Long, i As Long, CH As String
    L = Len(sIn)

    For i = 1 To L
        CH = Mid(sIn, i, 1)
        If IsNumeric(CH) Or CH = "/" Then
        Else
            CellTest = "No Good"
            Exit Function
        End If
    Next i

    CellTest = "Good"
End Function

enter image description here

answered Mar 17 '15 at 22:52

Gary's Student

95,722
10
59
99

This works well, however it returns "Good" when the cell is completely empty. – MapEngine Mar 18 '15 at 09:12

How do I check if a cell only contains characters from a specific list?

3 Answers3