2

I know I can create an "autofilter" at the top of an XLS table.. But how can I create a filter that filters for given strings?

example TABLE input:

[email protected]
[email protected]
[email protected]
[email protected]
[email protected]

after filter (filter for @FOO.com and @BAR.com):

[email protected]
[email protected]
[email protected]
2
  • 1
    You're asking for several programs at the same time. Given there's one Answer for Excel, one for OpenOffice and one for LibreOffice, which one would you accept? You can only accept one answer. Jan 13, 2014 at 9:41
  • at least for OpenOffice:\ sry Jan 14, 2014 at 11:00

2 Answers 2

1
+50

These are the instruction for Open Office:

  • Go to the top tool bar, and select Data.
  • Proceed to Select Filter and then Auto Filter.
  • Go to the column header of the email addresses you want to filter, click the dropdown, and select Standard Filter.
  • Enter the relevant fields: “ends with” for condition and “foo.com” for value OR “ends with” for condition and “bar.com” for value

That should give you the result you need. Hope this helps.

5

The answer to this is going to depend heavily upon what version of Excel you're using, but here are two ways of doing it:


Option One - A Helper Column
In a new column in your table enter the following formula:

=IF(OR(RIGHT([@Addresses],LEN([@Addresses])-FIND("@",[@Addresses]))="foo.com",RIGHT([@Addresses],LEN([@Addresses])-FIND("@",[@Addresses]))="bar.com"),1,0)


Now this is one ugly formula, it works in that you can now filter on all instances of 1 in this column, but what if you wanted to filter on something else?

This is where the best option comes in, though it's also the option most dependant on what you're using. I'm using Office Excel 2013.


Option Two - Text Filters
If you go to the Auto Filter dropdown you may find a feature called Text Filters. This will allow you to specify a huge range of methods on which to filter your data:

Text Filters


Click this and then set up your text filters. Here you can see the filters you've mentioned in your question:

ends with filters



This will give you far more flexibility, but as I said; it's far more dependent on what you're using.

2

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .