Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do you add three ending '0's to a column of numbers? | Excel Discussion (Misc queries) | |||
Custom Filter? | Excel Discussion (Misc queries) | |||
Custom Filter | Excel Worksheet Functions | |||
Custom Filter | New Users to Excel | |||
Custom Filter | Excel Discussion (Misc queries) |