Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filter first two characters
Hello,
I need to filter a column of data that has 4 characters on the first two characters only. 1100 1110 1140 1190 1550 1590 1600 1700 I want to filter everything starting with 11xx and 15xx. The list will vary by month but I will always need those two groups. Thanks! Mary |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filter first two characters
Assuming you're using AutoFilters...
Open filter, choose custom Criteria1, look for 11* Choose "or" Criteria2, look for 15* -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Mary" wrote: Hello, I need to filter a column of data that has 4 characters on the first two characters only. 1100 1110 1140 1190 1550 1590 1600 1700 I want to filter everything starting with 11xx and 15xx. The list will vary by month but I will always need those two groups. Thanks! Mary |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filter first two characters
If those are text values--not real numbers, you can use:
Begins with 11 or begins with 15 if they're real numbers, you could use a separate helper column with a formula like: =""&a2 (and drag down) Then filter on this helper column--since it's text. Or you could insert a helper column and use a formula like: =or(a2=1100,a2<1200,a2=1500,a2<1600) and filter that column to show Trues (xl2003 supports up to 2 criteria in the custom filter dialog--I don't recall what xl2007 supports.) Mary wrote: Hello, I need to filter a column of data that has 4 characters on the first two characters only. 1100 1110 1140 1190 1550 1590 1600 1700 I want to filter everything starting with 11xx and 15xx. The list will vary by month but I will always need those two groups. Thanks! Mary -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filter first two characters
Thank you both, I've got that piece working thanks to you!
This leads to my next question. Now that I've filtered the rows I want to see, how can I select just those rows and copy them to a new spreadsheet where I can do some calculations? Thanks again, Mary "Luke M" wrote: Assuming you're using AutoFilters... Open filter, choose custom Criteria1, look for 11* Choose "or" Criteria2, look for 15* -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Mary" wrote: Hello, I need to filter a column of data that has 4 characters on the first two characters only. 1100 1110 1140 1190 1550 1590 1600 1700 I want to filter everything starting with 11xx and 15xx. The list will vary by month but I will always need those two groups. Thanks! Mary |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filter first two characters
I spoke too soon - I found the anwer in a 2/16/09 using Edit, Find & Select,
Go to, Special, Visible Cells Only. "Luke M" wrote: Assuming you're using AutoFilters... Open filter, choose custom Criteria1, look for 11* Choose "or" Criteria2, look for 15* -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Mary" wrote: Hello, I need to filter a column of data that has 4 characters on the first two characters only. 1100 1110 1140 1190 1550 1590 1600 1700 I want to filter everything starting with 11xx and 15xx. The list will vary by month but I will always need those two groups. Thanks! Mary |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filter first two characters
do the filter
select the range to copy Edit|goto (or F5 or ctrl-g) Special|visible cells only and do your copy|paste Mary wrote: Thank you both, I've got that piece working thanks to you! This leads to my next question. Now that I've filtered the rows I want to see, how can I select just those rows and copy them to a new spreadsheet where I can do some calculations? Thanks again, Mary "Luke M" wrote: Assuming you're using AutoFilters... Open filter, choose custom Criteria1, look for 11* Choose "or" Criteria2, look for 15* -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Mary" wrote: Hello, I need to filter a column of data that has 4 characters on the first two characters only. 1100 1110 1140 1190 1550 1590 1600 1700 I want to filter everything starting with 11xx and 15xx. The list will vary by month but I will always need those two groups. Thanks! Mary -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I filter for cells that contain 3 or less characters | Excel Discussion (Misc queries) | |||
Custom filter will not return characters between 1-9 inclusive | Excel Discussion (Misc queries) | |||
In Excel find characters when multiple characters exist w/i a cel | Excel Worksheet Functions | |||
HOW DO I EXTRACT ALL CHARACTERS AFTER 5 CHARACTERS ON LEFT | Excel Worksheet Functions | |||
how do i filter data by number of characters in excel? | Excel Discussion (Misc queries) |