CODEWITHSUNDEEP
excelexcel-formulacharactercontainsvba
MapEngine
MapEngine

Reputation: 553

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

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):

https://i.imgur.com/IrJuZ47.png

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?

Upvotes: 2

Views: 5357

Answers (3)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60474

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.

Upvotes: 1

pnuts
pnuts

Reputation: 59495

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")

Upvotes: 3

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96791

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

Upvotes: 0

Related Questions