Replace One of 2 Words in Calc

Hi everybody,

I have more than about 30000 cells with HTML codes and other stuffs.
I want to remove all of the contents of each cells before 2 tags.
for example, this is a sample of a cell:

...<UN> ... <VI> ... <UN> ... <UN> ... <VI> ...

but another cell looks like this :

...<VI> ... <VI> ... <UN> ... <VI> ... <UN> ...

I want to remove all of the contents before <UN> and <VI> separately for each cell, and it depends on which one comes first. I want Calc search from the beginning of a cell and look for one of these 2 tags, and would stop searching when one of them appeared and remove before of it whether it is <UN> or <VI> and go for next cell, do the same and so on … .

I know that with the code ^.*?<VI> and replace it with nothing I can remove all of the codes before <VI> but it doesn’t help. Because in many cells <UN> is the first tag and <VI> comes after that and vice versa. And it’s obvious I don’t want to miss any of them.

I would appreciate your input in advance.

Thanks.

search ^.+?(<UN>|<VI>)(.*)$ and replace with $1$2

Caveat, that may garble things because it also matches content that has no text before or and just matches the second occurrence, e.g. <VI> bbb <VI> is replaced with <VI> which certainly is not wanted.

thank you for your answer.

  • Find: ^(?!<UN>|<VI>)(.(?!<UN>|<VI>))*.(<UN>|<VI>)(.*)
  • Replace: $2$3

Matches only if content does not start with <UN> or <VI> and there is any text followed by a first <UN> or <VI>.

Thanks a lot erAck. It works !

How about that:

search: ^.*?(<UN>|<VI>)

replace: $1

Thanks for your help.

This one also works, but it unnecessarily replaces content that starts with <UN> or <VI> with the same text. The expression given in the accepted answer doesn’t even match such, hence needs less operations to succeed in a ReplaceAll on a selection, or less clicks on FindNext when manually advancing to the next cell and such content is present.