How to filter numbers by first digit ?

[nemo66ro]

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:

Please Login or Register  to view this content.

Taking into account that I should get something like

Please Login or Register  to view this content.

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

Daniel

[dilipandey]

Hi Daniel,

I would suggest you to use left or mid function to obtain the first digit and then you can filter / sort them easily.. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[nemo66ro]

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.

[thameem127]

Here it is. Check this attachment extract 9 macro.xlsm
Thanks

Thameem

[watersev]

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

[nemo66ro]

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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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

[watersev]

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

Please Login or Register  to view this content.

[nemo66ro]

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:

Please Login or Register  to view this content.

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