Custom Filter for ending numbers

  • Thread starter Thread starter Brian17
  • Start date Start date
B

Brian17

Hi, I have a quesiton about filtering. I have a column that contains entries
such as:
aa1100
bb2200
cc4455
cfd9088
fdaniels
fdr8888
jjones
jsmith
kwilliams

How can I filter the list so I see only the entries that do not contain
numbers? I've tried to figure this out using the "ends with" custom filter
option, but can't get it to look for ending in 4 digits. The data will always
end in 4 digits. Any thoughts on filtering it or using a function?

Thanks!
-Brian
 
I'd use a helper column that would evaluate to true or false.

If I only cared about the rightmost character, I'd use a formula like:

=ISNumber(-Right(A2,1))

Then drag down and filter by that helper column.

===
If you really wanted to check the last 4 characters to see if they were numeric:
=ISNumber(-Right(A2,4))
 
Thanks for the reply Dave, I tried both formulas listed below and they didn't
seem to return the correct results. I used the same list from my original
post and in some of the entries where I have the mix of letters and numbers
it returns a FALSE.

Any thoughts?

-Brian
*************
 
Thanks for the reply Dave, I tried both formulas listed below and they didn't
seem to return the correct results. I used the same list from my original
post and in some of the entries where I have the mix of letters and numbers
it returns a FALSE.

Any thoughts?

-Brian
******************
 
What formula did you use?
What was in the cell that caused the formula not to be correct?
 
Have you checked that you don't have spaces after your letters and numbers?
What do you see with the formula =RIGHT(A2,4) ?
 
Hi Dave, thanks again for the reply. Sorry for the double posts, technical
glitch when posting.

Here is what my sheet looks like:
A B C
aa1100 TRUE TRUE
bb2200 TRUE TRUE
cc4455 TRUE TRUE
cfd9088 FALSE FALSE
fdaniels TRUE TRUE
fdr8888 FALSE FALSE
jjones FALSE FALSE
jsmith FALSE FALSE
kwilliams FALSE FALSE

B column consists of: =ISNUMBER(-RIGHT(A2,4))
C column consists of: =ISNUMBER(-RIGHT(A2,1))

Each is filled down to the last entry in A column.

No spaces in data in A column. Why do the entries for A4 and A6 generate
FALSE?

Thanks!
-Brian
*****************
 
Yes no spaces in data in a column. With the =right formula we see a mixture
of numbers and letters. I know I can use that one to find the last 4
characters and then sort the list to get all the numbers together and all the
letters together, but we are looking for a filter to do this in 1 or 2 steps,
if possible.

Any further thoughts on either a custom or advanced filter?
Thanks!
-Brian
 
You still haven't told us exactly what =RIGHT(A2,4) shows when
=ISNumber(-Right(A2,4)) is returning false in the cases where you expect
TRUE, so we can't help you.
I would still suspect that you have either spaces or other non-printing
characters after your data.
--
David Biddulph

Brian17 said:
Yes no spaces in data in a column. With the =right formula we see a
mixture
of numbers and letters. I know I can use that one to find the last 4
characters and then sort the list to get all the numbers together and all
the
letters together, but we are looking for a filter to do this in 1 or 2
steps,
if possible.

Any further thoughts on either a custom or advanced filter?
Thanks!
-Brian
Click to expand...
 
It kind of looks like the formulas you used on row 1 pointed to row 2.

Make sure you point at the correct cell--on the same row.
Hi Dave, thanks again for the reply. Sorry for the double posts, technical
glitch when posting.

Here is what my sheet looks like:
A B C
aa1100 TRUE TRUE
bb2200 TRUE TRUE
cc4455 TRUE TRUE
cfd9088 FALSE FALSE
fdaniels TRUE TRUE
fdr8888 FALSE FALSE
jjones FALSE FALSE
jsmith FALSE FALSE
kwilliams FALSE FALSE

B column consists of: =ISNUMBER(-RIGHT(A2,4))
C column consists of: =ISNUMBER(-RIGHT(A2,1))

Each is filled down to the last entry in A column.

No spaces in data in A column. Why do the entries for A4 and A6 generate
FALSE?

Thanks!
-Brian
*****************
Click to expand...
 
Yes you are correct! The formula in B1 was referencing A2. When filled down,
of course, it returned incorrect results. Thanks for spotting that.

So, this is the only way to do this and there is no quick custom filter to
do this? Not a problem if it is, just curious.

Thanks!
-Brian
*******************
 
No spaces or toher characters. I was referencing the worng cell in the first
instance of the formula. See the other posts in this thread. Thanks for your
input.

-Brian
*************
 
That's the only way I know if I want to use Autofilter.
Yes you are correct! The formula in B1 was referencing A2. When filled down,
of course, it returned incorrect results. Thanks for spotting that.

So, this is the only way to do this and there is no quick custom filter to
do this? Not a problem if it is, just curious.

Thanks!
-Brian
*******************
Click to expand...
 
Back
Top