Custom Filter for ending numbers
 

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

Custom Filter for ending numbers
 

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

Brian17 wrote:

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

--

Dave Peterson

Custom Filter for ending numbers
 

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
*************
"Dave Peterson" wrote:

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

Brian17 wrote:

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

--

Dave Peterson

Custom Filter for ending numbers
 

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
******************

"Dave Peterson" wrote:

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

Brian17 wrote:

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

--

Dave Peterson

Custom Filter for ending numbers
 

What formula did you use?
What was in the cell that caused the formula not to be correct?

Brian17 wrote:

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
******************

"Dave Peterson" wrote:

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

Brian17 wrote:

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

--

Dave Peterson


--

Dave Peterson

Custom Filter for ending numbers
 

Have you checked that you don't have spaces after your letters and numbers?
What do you see with the formula =RIGHT(A2,4) ?
--
David Biddulph

"Brian17" wrote in message
...
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
*************
"Dave Peterson" wrote:

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

Brian17 wrote:

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

--

Dave Peterson

Custom Filter for ending numbers
 

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
*****************

"Dave Peterson" wrote:

What formula did you use?
What was in the cell that caused the formula not to be correct?

Brian17 wrote:

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
******************

"Dave Peterson" wrote:

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

Brian17 wrote:

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

--

Dave Peterson


--

Dave Peterson

Custom Filter for ending numbers
 

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

"David Biddulph" wrote:

Have you checked that you don't have spaces after your letters and numbers?
What do you see with the formula =RIGHT(A2,4) ?
--
David Biddulph

"Brian17" wrote in message
...
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
*************
"Dave Peterson" wrote:

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

Brian17 wrote:

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

--

Dave Peterson

Custom Filter for ending numbers
 

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" wrote in message
...
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

"David Biddulph" wrote:

Have you checked that you don't have spaces after your letters and
numbers?
What do you see with the formula =RIGHT(A2,4) ?
--
David Biddulph

"Brian17" wrote in message
...
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
*************
"Dave Peterson" wrote:

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

Brian17 wrote:

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

--

Dave Peterson

Custom Filter for ending numbers
 

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.

Brian17 wrote:

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
*****************

"Dave Peterson" wrote:

What formula did you use?
What was in the cell that caused the formula not to be correct?

Brian17 wrote:

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
******************

"Dave Peterson" wrote:

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

Brian17 wrote:

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

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Custom Filter for ending numbers
 

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
*******************

"Dave Peterson" wrote:

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.

Brian17 wrote:

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
*****************

"Dave Peterson" wrote:

What formula did you use?
What was in the cell that caused the formula not to be correct?

Brian17 wrote:

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
******************

"Dave Peterson" wrote:

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

Brian17 wrote:

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

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Custom Filter for ending numbers
 

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
*************

"David Biddulph" wrote:

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" wrote in message
...
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

"David Biddulph" wrote:

Have you checked that you don't have spaces after your letters and
numbers?
What do you see with the formula =RIGHT(A2,4) ?
--
David Biddulph

"Brian17" wrote in message
...
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
*************
"Dave Peterson" wrote:

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

Brian17 wrote:

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

--

Dave Peterson

Custom Filter for ending numbers
 

That's the only way I know if I want to use Autofilter.

Brian17 wrote:

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
*******************

"Dave Peterson" wrote:

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.

Brian17 wrote:

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
*****************

"Dave Peterson" wrote:

What formula did you use?
What was in the cell that caused the formula not to be correct?

Brian17 wrote:

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
******************

"Dave Peterson" wrote:

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

Brian17 wrote:

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

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson