Excel - Filter first two characters

Asked By Mar on 12-Mar-09 10:25 AM
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




Luke replied on 12-Mar-09 10:43 AM
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!*
Dave Peterson replied on 12-Mar-09 11:10 AM
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.)




--

Dave Peterson
Mar replied on 12-Mar-09 01:13 PM
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
Mar replied on 12-Mar-09 01:22 PM
I spoke too soon - I found the anwer in a 2/16/09 using Edit, Find & Select,
Go to, Special, Visible Cells Only.
Dave Peterson replied on 12-Mar-09 01:25 PM
do the filter
select the range to copy
Edit|goto (or F5 or ctrl-g)
Special|visible cells only
and do your copy|paste




--

Dave Peterson