Ask Your Question

Replace One of 2 Words in Calc

asked 2017-02-21 15:51:24 +0200

alibsd700 gravatar image

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.


edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted

answered 2017-02-21 17:25:46 +0200

erAck gravatar image
  • 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>.

edit flag offensive delete link more


Thanks a lot erAck. It works !

alibsd700 gravatar imagealibsd700 ( 2017-02-22 05:19:46 +0200 )edit

answered 2017-02-21 16:34:00 +0200

karolus gravatar image

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

edit flag offensive delete link more


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

erAck gravatar imageerAck ( 2017-02-21 17:14:12 +0200 )edit

thank you for your answer.

alibsd700 gravatar imagealibsd700 ( 2017-02-22 05:21:33 +0200 )edit

answered 2017-02-21 20:42:20 +0200

Gilward Kukel gravatar image

updated 2017-02-21 21:17:57 +0200

How about that:

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

replace: $1

edit flag offensive delete link more


Thanks for your help.

alibsd700 gravatar imagealibsd700 ( 2017-02-22 05:17:32 +0200 )edit

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.

erAck gravatar imageerAck ( 2017-02-22 21:40:36 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-02-21 15:51:24 +0200

Seen: 66 times

Last updated: Feb 21 '17