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

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

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
>

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
>

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

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" <(E-Mail Removed)> wrote in message
news:0A829746-0150-40D7-AEAE-(E-Mail Removed)...
> 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
>>

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
>

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" <(E-Mail Removed)> wrote in message
> news:0A829746-0150-40D7-AEAE-(E-Mail Removed)...
> > 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
> >>
>
>
>

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" <(E-Mail Removed)> wrote in message
news:6344191A-5678-42E0-A145-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:0A829746-0150-40D7-AEAE-(E-Mail Removed)...
>> > 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
>> >>
>>
>>
>>

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