AutoFilter for cells contains exactly specific number

Question

I have a column with values like the below sample:

Size
4
1*4
1*24
4*1
4.5*10
2*14*5
3*4*5

I need to set a filter to get the cells contains the specific number e.g "4" ,

the expected results are (4 , 1*4 , 4*1 , 3*4*5).

If I used wildcards "*4*" as criteria then it will give me all values contains "4" like (1*24 , 4.5*10) and that not required.
the below code only find the cells that begins with my number:

Sub AutoFilter_on_number()

    Dim ws As Worksheet, rng As Range

    Const filterColumn As Long = 29  'column "AC"
    
    Set ws = ActiveSheet
    
    Set rng = ws.Range("A2:AH7000")
    
    rng.AutoFilter Field:=filterColumn, Criteria1:="=4*", Operator:=xlFilterValues
    
End Sub

Urbani

Answer 1

Autofilter On Number

Sub AutoFilterOnNumber()

    ' Define constants.
    
    Const F_COLUMN As Long = 29
    Const F_CRITERION As String = "4"
    Const F_DELIMITER As String = "*"
    
    ' Reference the table range.
    
    Dim rg As Range
    
    With ActiveSheet ' improve!
        If .FilterMode Then .ShowAllData ' clear filters
        If .AutoFilterMode Then .AutoFilterMode = False ' turn off auto filter
        Set rg = .Range("A1").CurrentRegion
    End With
    
    ' Write the values from the critical column of the range to an array.
    
    Dim rCount As Long: rCount = rg.Rows.Count - 1
    Dim Data():
    Data = rg.Columns(F_COLUMN).Resize(rCount).Offset(1).Value
        
    ' Write the matching strings to the keys (a 1D array) of a dictionary.
    
    ' Define the dictionary.
    Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
    
    ' Declare variables to be introduced in the For...Next loop.
    Dim SubStrings() As String, r As Long, rStr As String
    
    ' Loop through the elements of the array.
    For r = 1 To rCount
        ' Convert the current value to a string and store it in a variable.
        rStr = Data(r, 1)
        If Len(rStr) > 0 Then ' is not blank
            ' Split the string into an array.
            SubStrings = Split(rStr, F_DELIMITER)
            ' Attempt to match the criterion in the split array.
            If IsNumeric(Application.Match(F_CRITERION, SubStrings, 0)) Then
                If Not dict.Exists(rStr) Then ' not in the dictionary
                    dict(rStr) = Empty
                'Else ' already in the dictionary; do nothing
                End If
            'Else ' criterion not found, it's an error value; do nothing
            End If
        'Else ' is blank; do nothing
        End If
    Next r
    
    ' Filter the table range.
    
    If dict.Count > 0 Then ' there are rows to be filtered
        ' Use the keys (a 1D array) of the dictionary
        ' with 'xlFilterValues' to filter the data.
        rg.AutoFilter F_COLUMN, dict.Keys, xlFilterValues
    'Else ' no rows to be filtered; do nothing
    End If
    
End Sub

Egret

I have run a quick test and it works perfectly, Please, can you add some comments to make your code fully understandable for me? – Urbani

Answer 2

If you're happy using a helper column, this can be done with just a formula:

=IF(ISERROR(FIND("x4x","x"&SUBSTITUTE(A2,"*","x")&"x")),FALSE,TRUE)

Then simply filter on column B result.

Output:

Inartificial

I tried with helper column and it works. Please, if I replaced all asterisks "*" with another character e.g "x" , Is there availability for an answer without using a helper column? – Urbani

You would need to replace the '*' with 'x', but also wrap the string in x's - e.g. x<string>x in a similar way to my formula. Then you just filter on 'x4x'.. – Inartificial

Answer 3

If a helper column is okay:
Create a column with the formula =IF(ISERROR(SEARCH("~*4~*", "*"&A2&"*")),"NO","YES") (assuming that your data starts in A2).

The original cell content is enclosed with extra leading and trailing asterix (eg 4*1 will be *4*1*). Now we are sure that the cells you are interested in contain the string *4*. You could use this string and set an autofilter, however, it is a little bit ugly, therefore I use SEARCH and an IF to check if the helper column contains *4* and return either YES or NO. Now it's easy to filter.

Just note that you have to mask the asterix with a leading tilde character to prevent that Excel sees it as wild character (that is true both when using it in the formula above or in the auto filter criteria). This is the reason for the strange looking "~*4~*" as first parameter for the search function.

Rotatory