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:

LASFD@FOO.COM
LKXYCVER@FOO.COM
salfdajlfd@sadfjasdf.com
LAKSJDFA@LASJFDSA.COM
SADLKFJALD@BAR.COM

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

LASFD@FOO.COM
LKXYCVER@FOO.COM
SADLKFJALD@BAR.COM
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. – Thomas Weller Jan 13 '14 at 9:41
  • at least for OpenOffice:\ sry – gasko peter Jan 14 '14 at 11:00
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.