I need help.
I have a thousand rows in 1 column. Each rows either end with a digit or a letter. And this letter ranges from A until RR. How can I select all the values that end with digit only?
Your help is much appreciated. Please respect my post.
I tried to used "not ending with" option in Filter. But i can only have 2. Is there a way to range the filter values from A through RR?
Assuming the input data is in column A, I consider the entire column, but you can adjust it to your specific range, then you can use:
=FILTER(A:A, ISNUMBER(-RIGHT(A:A,1)))
It returns the rows from column A that ends with a single digit. We use the minus sign (-)to convert a text into a number. If it is not a number returns #VALUE! and in that case ISNUMBER returns FALSE, otherwise it returns TRUE.
If your Excel version doesn't support FILTER function, the following answer to the question: What is an alternative of FILTER function for other than office365 excel? provided by @P.b can be adapted to your case as follows:
=IFERROR(INDEX(A:A,SMALL(IF(ISNUMBER(-RIGHT(A:A,1)),ROW(A:A)),
ROWS($1:1))),"")
Then just expand it down.
Please take into account that using the entire column is not efficient, better to adapt it to your specific range. Using a specific range for example A1:A3. The previous formula can be modified to return the entire array, there is no need to expand it down:
=IFERROR(INDEX(A1:A3,SMALL(IF(ISNUMBER(-RIGHT(A1:A3,1)),ROW(A1:A3)),
ROW(A1:A3))),"")