Share via

How do I select and delete all rows matching some criteria? (Same as find and select in Excel on Windows)

Anonymous
2015-01-13T12:49:20+00:00

I have Excel for Mac, 2011

I have a large spreadsheet and need to remove all rows that contain a specific text in one of the columns.

For example, it could be a bank statement with thousands of rows and I want to remove all non-deductables from it, so I search for all the rows that contain, say "Amazon" and delete them. Then all the rows that contain "Curry Shop" and delete them.

Under the windows version I can use "Find & Select" on the home ribbon to find all rows containing specific text and the use "delete sheet rows" from the home ribbon to delete them all.

How can I do the same thing in the Mac version?

Thanks in advance!

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Anonymous
    2015-01-13T15:05:13+00:00

    Not work much in the Mac version with things like this but maybe this is a option for you on the Mac

    Right click on the cell with the value you want to delete and choose Filter by Cell value

    Now select the filtered cells and right click and choose delete row and then clear the Filter

    30+ people found this answer helpful.
    0 comments
Answer accepted by question author
  1. Bob Jones AKA CyberTaz MVP 435K Reputation points
    2015-01-13T15:44:40+00:00

    There is no equivalent feature in Mac Excel.

    Short of a VBA solution, the closest you an get is the suggestion offered by Ron.

    3 people found this answer helpful.
    0 comments

6 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2017-01-02T14:22:22+00:00

    I found this a useful workaround without having to resort to VBA:

    1. Use conditional formatting (highlight cell rules -> text that contains...)
    2. Insert the text you want highlighted. Do this multiple times if required (e.g. if you want all cells which have the word 'the' OR the word 'a', etc.).
    3. Use a color filter (data -> filter) to show only the highlighted cells.
    4. Select all highlighted cells and clear contents or delete.

    10+ people found this answer helpful.
    0 comments
  2. Anonymous
    2015-01-13T16:00:22+00:00

    For the OP, if you want to use VBA you can look at this page on my site.

    Created for Excel for Windows but everything must work on the Mac also as far as I know

    http://www.rondebruin.nl/win/s4/win001.htm

    0 comments
  3. Anonymous
    2015-01-13T15:04:29+00:00

    Yes there is an equivalent feature in Excel

    View this Movie and see if this is what you're looking for:

    Find / Replace in Excel 

    Note movie is MP4 format. If you can not view I will come back and post a YouTube version. 

    _________

    **Disclaimer:**The opinions & replies are solely mine and mine alone, and do not necessarily reflect upon my position as a Community Moderator.

    _________

    Disclaimer: Recent upgrade to the "Answers System" up to this Point has been less than stelar.

    Distorted Avatars, Missing Avatars, inserted Graphics going missing of their own accord.

    If any replies of mine exhibit these symptoms try reloading / refreshing page, or coming back another Hour or day, until resolved. You may have to ask me to repeat or send links to images instead of sending actual images, until if and when, they can get this mess straightened out.

    0 comments