How do you delete rows with common search string?

I cannot figure out how to delete row(s) that have a common string or value.
A text file was imported containing a multitude of uneeded, identical lines(rows).
The find/replace seems to be limited to replacing character strings only.
I suspect a macro is needed but before I spend time on that I wonder if there is a simple approach?

Instead of deleting these rows, you can copy only rows you need.

  1. First, mark rows you don’t need on an unused column.
  • If all repeating lines are the same, you can use =A2="preset text to filter" to auto-mark.
  • Otherwise, you can make list of these lines and use =MATCH(A2;range_with_lines_to_filter;0).
  • And you can even create Pivot table with source lines on rows and their count on data, sorted by count, to find repeating data. And then use top lines of that pivot in MATCH of previous bullet.
  1. Then filter them out, Ctrl+A, Ctrl+V to new sheet.

Or vice versa, you can mark only lines you need, then filter them in.

There is a simple approach. It sounds like you are mostly trying to remove duplicates—you mention unneeded, identical rows. All you have to do is:

  1. Select the data you need to remove. Or just click anywhere within the data—don’t bother working to select the whole data range; if it’s contiguous (no gaps), Calc will figure it out automatically.
  2. On the menu bar, go to Data → Filter → Standard Filter…
  3. The default criteria will show “Field name” [whatever the column heading is], “condition” as equals, and “value” blank. Choose “not empty” for the value so everything in your grid will match the criteria.
  4. Click the “More options” button near the bottom.
  5. Check the “No duplication” box.
  6. Check the “Copy results to…” box, and choose where you want the de-duped list to be copied to.
  7. Click “OK”.

If the original spreadsheet had typos in it you still might have some extra rows, like a row for “Zebras” and an extra one for “Zebars”, but it should be a small number of them if any and you can remove those manually.

1 Like