How to filter numbers by first digit ?

Hello guys,

I have a table with many rows (over 10000) and I would like to filter all cells by the first digit of a column, let say C.
This column has different numbers and simply I don't know how to filter them so that to keep only those numbers which start with 9. Of course, I would prefer to avoid any indirect method (looping, ordering + finding first and last relevant row, and so on) and to use the common AutoFilter feature, which is simpler and fast.
Basically, the column that should be filtered is like below:

3161131
9161132
2039811
9670316
1670317
9161131
6670325
...........

Taking into account that I should get something like

9161132
9670316
9161131
...........

are there any way (at code level) to turn AutoFilter method to advantage ?
Thank you in advance.

Daniel

Hello Dilipandey,

Thank you for your quick response. As we know, left or mid function involves a huge looping through each row of the sheet....If I would like after that to evaluate other digit, I consider this would not be a solution for saving time.
Thank you for your interests in this issue.

hi nemo66ro, option, please check attachment, select starting digit to filter list and press Run button

Hello guys,
Both macro succeed to extract the numbers by first digit. However, the second seems to be more interesting as long as I work with arrays. I have some doubts that thameem127's approach could load the results in an array directly (as I know we have to declare a specific header for columns so that I think it is more suitable for calculations on sheet).

Range("D5:E12").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("H3:I4"), _
CopyToRange:=Range("J5:K5"), Unique:=False

Regarding to the watersev code my issue is this is less readable...For example, how could I understand the code line below ?

x = Application.Transpose(Filter(Split(Replace("|#" & Join(Application.Transpose(Range("a2", Cells(Rows.Count, 1).End(xlUp))), "|#"), "#" & fval, fval), "|"), "#", 0))

It seems we have no less than 6 functions involved in one row...Watersev, do you think is possible to make this line more readable ? I appreciate your help this solving my issue but in the same time I would like learn something. Thank you in advance.

Daniel

that's an alternative to filter usage, you can split that line to:

arr = Application.Transpose(Range("a2", Cells(Rows.Count, 1).End(xlUp))) 'one-dimensional array
str1 = "|#" & Join(arr, "|#") 'join array 
str2 = Replace(str1, "#" & fval, fval) 'replace # & number to number only
arr1 = Split(str2, "|") 'create array by splitting 
arr2 = Filter(arr1, "#", 0) 'filter array with elements without #
arr3 = Application.Transpose(arr3) 'transpose to output result

Hello everybody,

I have searched online a solution to this issue and it is first time when I find one. Thanks to watersev I think many users will be able to go beyond an excel limitation: respectively, to use the Autofilter property "begins with" not just for text format data but for numbers too. Because I have considered this kind of filtering could be very useful in many cases, I have written a function so that to make entire solution more intelligible for everyone:

Option Base 1
Sub filterCaling()
   Dim myFilter As Variant, U As Range
   Set U = Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
   'myFilter array will contain all numbers that "begins with" 1
   myFilter = FilterByDigit(U, 1)
   'If we want, we can transpose the results somewhere in an empty column
   Range("B2:B" & 2 + UBound(myFilter) - 1).Value = WorksheetFunction.Transpose(myFilter)
   Set U = Nothing
End Sub

Private Function FilterByDigit(myRange As Range, digit As String)    'by watersev
    Dim arr1 As Variant, arr2 As Variant
    Dim str1 As String, str2 As String

    arr1 = WorksheetFunction.Transpose(myRange) 'Create one-dimensional array
    str1 = "#" & Join(arr1, "|#")                            'Join this array, delimited by "|#"
    str2 = Replace(str1, "#" & digit, digit)               'Replace "# & number" to number only
    arr2 = Split(str2, "|")                                     'Create a new array by splitting
    arr1 = Filter(arr2, "#", False)                           'Filter last array with elements without #
    'by mikerikson's idea: Restructurate an array so that to have base 1 ...
    FilterByDigit = WorksheetFunction.Index(arr1, 1, 0)
End Function

In the code lines above I have modified str1 = "|#" with str1 = "#" because in the splitting function I had getting
an inconvenient position of the first value...
Five globes for this solving, watersev. Many thanks.

Daniel