Check for specific digits within number

[YasserKhalil]

Hello everyone
I have numbers in A2:A & lastrow
These numbers are to be checked ..searching for the digits (0 - 2 - 5 - 8 ) ..if these digits are found, then in column L to type "Yes" .. if not leave it empty

For example:
A2 : 2850850 (this number has the digits 0 & 2 & 5 & 8) so in L2 need to type "Yes"
A3 : 28513258 (this number has only 2 & 5 & 8) so in L2 I need to leave it blank

Hope it is clear

 

[YasserKhalil]

Thanks I tried this and it is ok

Sub Test()
    Dim I As Long
    For I = 2 To Cells(Rows.Count, 1).End(xlUp).Row
        If Digit(Cells(I, 1), 0) >= 1 And Digit(Cells(I, 1), 2) >= 1 And Digit(Cells(I, 1), 5) >= 1 And Digit(Cells(I, 1), 8) >= 1 Then
            Cells(I, "L").Value = "Yes"
        End If
    Next I
End Sub

Function Digit(R As Range, N As Integer) As Integer
    Dim I As Integer, S As String
    S = R.Value
   
    For I = 1 To Len(S)
        If Mid(S, I, 1) = N Then Digit = Digit + 1
    Next I
End Function

But I need to create UDF function to count unique digits within each cell
For example: 2850850 has four digits only (unqiue)

 

[Luke M]

this should work.

Function UniqueDigits(xNum As String) As Long
Dim i As Integer

For i = 0 To 9
    If InStr(1, xNum, i) Then
        UniqueDigits = UniqueDigits + 1
    End If
Next i
End Function

 

[YasserKhalil]

Thank you very much Mr. Luke
You are great

How can I convert this code to UDF function (without another sub)
I need to use it directly in the worksheet

Sub Test()
    Dim I As Long
    For I = 2 To Cells(Rows.Count, 1).End(xlUp).Row
        If Digit(Cells(I, 1), 0) >= 1 And Digit(Cells(I, 1), 2) >= 1 And Digit(Cells(I, 1), 5) >= 1 And Digit(Cells(I, 1), 8) >= 1 Then
            Cells(I, "L").Value = "Yes"
        End If
    Next I
End Sub

Function Digit(R As Range, N As Integer) As Integer
    Dim I As Integer, S As String
    S = R.Value
 
    For I = 1 To Len(S)
        If Mid(S, I, 1) = N Then Digit = Digit + 1
    Next I
End Function

Is it be possible by formulas?

 

[p45cal]

=IF(ISERROR(FIND(0,F2) & FIND(2,F2) & FIND(5,F2) & FIND(8,F2)),"","Yes")
where F2 contains your number.

 

[YasserKhalil]

That's awesome and simple formula
Thanks a lot for this great help

 

[Khalid NGO]

Hi Yasser,

Another option could be:
=SIGN(AND(ISNUMBER(FIND({0,2,5,8},A2))))

or to shorten more:
=AND(ISNUMBER(FIND({0,2,5,8},A2)))+0

Format the cell as:
"Yes";;;

Regards,

 

[YasserKhalil]

Really great Mr. Khalid
I liked your formula a lot. It is wonderful
Thank you very much for sharing my issue