How can I remove data that has a specific pattern from spreadsheets?

Dear IT gurus,

I need to remove some data from some spreadsheets. I would like to know if there is an easy way to do it.

What I need to do is this:

I have these types of entries:

< abc > < xyz >

My question is this: Is there a simple way to extract all such entries without having to do it for each entry,i.e., is there a way I can remove all the < > brackets and data between them in one go?

If you know how to do this I would be grateful if you would let me know what I need to do.

Best wishes,

Techno Peasant

Techno Peasant – Still looking for help here?

You don’t need a macro to do this - regular expressions should be able to sort you out.

  • Press Ctrl-H to bring up the full Find and Replace dialogue box.

  • Click the More Options button if you see it at the bottom right.

  • Check the ‘Regular expressions’ checkbox

  • Now, in the ‘Search for’ combo-box at the top, enter:


  • Leave the ‘Replace with’ combo-box blank.

Now when you click ‘Replace’, this should go through your spreadsheet finding any instance of a left angle-bracket (<) followed by any number of any characters (.*) followed by a right angle-bracket (>) and replace it with nothing, i.e., remove it.

@Doubi, If LO uses greedy-matching, wouldn’t that mean that your REGEXP would change something like foo stuff to keep bar to the empty string “” instead of the intended " stuff to keep "?

(If LO uses non-greedy matching, then we’d get "foo stuff to keep bar")

@qubit1, You’re quite right. I’ve just checked, and LO does indeed use greedy matching. Therefore, to deal with the scenario you cited (slightly different from that of the OP, mind you), one would need the pattern <.*?>, with the question mark ‘?’ being the non-greedy operator, i.e. saying, "please match as few characters as possible.
All that said: your example looks far more like HTML/XML than does the OP’s, which looks more like just some unusually delimited data. And as we all know, you can’t parse (X)HTML with regex :slight_smile: