List specific number

[Tom.Jones]

Howdy,

In column A is a list of numbers.
What formula should I use to list the first only 7 numbers in column A, whose last digit is 8.

Thank you.

 

[Jon von der Heyden]

Glad it's working for you. Another VBA approach:

Public Sub NotAFormula()
    Dim varVals             As Variant
    Dim lngItem             As Long
    Dim lngCount            As Long
    Dim varResults(1 To 7) As Variant
    
    varVals = Filter(Application.Transpose(Sheet1.Range("A2:A18").Value), "8", True)
    
    For lngItem = LBound(varVals) To UBound(varVals)
        If Right$(varVals(lngItem), 1) = "8" Then
            lngCount = lngCount + 1
            varResults(lngCount) = varVals(lngItem)
        End If
        If lngCount = 7 Then Exit For
    Next lngItem
    
    Sheet1.Range("B2:B8").Value = Application.Transpose(varResults)
End Sub

 

[Tom.Jones]

Thank you so much Jon.