Ask Your Question

find/replace: if keyword found inside cell, overwrite complete cell with new keyword

asked 2015-01-16 21:27:38 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

This is about LO Calc v4.2.7.2 in German on Linux Mint 17 with Xfce, German.

I don't know if the title explains it very well, I'll give an example.

In a large Calc worksheet, I do a search for all the cells that contain the word "Hannover". All these cells may or may not contain other words as well. However, I want to perform a replacement operation on all these cells that result in them containing only the word "Niedersachsen".

What find/replace would normally do is to replace all instances of the word Hannover with Niedersachsen and leave other words in the same place untouched.

But what I'd like to do is find an automatted way to replace everything in cells that contain the keyword, as opposed to replacing just the keyword itself. In other words: "Dear Calc, if you find this keyword in any cell, overwrite all its contents with this other keyword". Is there any way to do this through a regular expression maybe?

edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted

answered 2015-01-25 15:13:30 +0200

cikousek gravatar image

Hi guys,

Not sure this is the right place to ask, but it is quite related to what I need to solve. I don´t know how to make CALC search for a cell in an array. The problem is that it is possible the searched values are not exactly the same (e.g. search for "22" and find "22", "test 22", "22 test" etc. as if you´d find the results using ctrl+f). In Excel I used MATCH(" * "&B2&" * ";M1:M55;0) (no spaces between " and * of course) to find the row the value is in. Unfortunately, the CALC doesn´t accept " * "&B2&" * " or anything similar or the . together with * that was mentioned above. Could you please advise, how to force CALC to find the value if it is also a part of the cell value? It could be text, number as well as combination of text and numbers.

Thanks in advance.

edit flag offensive delete link more


The original question was in the direction of "Find & Replace". The subject indicated that clearly. Your question is about "using MATCH() with patterns to search for" or similar. Seemingly you are accustomed to the MS "wildcard concept" to a degree whilst LibreOffice is using the concept of "regular expressions" which is only superficially similar. Please post your question under a new more appropriate subject and you will surely get useful answers.

Lupp gravatar imageLupp ( 2015-01-25 16:23:37 +0200 )edit

Yes, please don't post a question as an answer to another question. @cikousek

LogicDaemon gravatar imageLogicDaemon ( 2015-03-07 08:06:37 +0200 )edit

answered 2015-01-16 22:32:19 +0200

LogicDaemon gravatar image

You'll need to use regular expressions. Check this option in Search and Replace dialogue box, then Search for: .*what you want to find.* (. means any char, * means 0 or more of previous characters).

It will replace whole cells that way.

Be careful though, if you'll include regular expressions character in your what you want to find text, they will be specially processed. For first time, screen anything but letters and digits (and spaces) with \. Like in: .*Text to find\. Really\. And some email\@dom\.com.* (note I don't screen . and * at the begin and end, so they work as intended)

edit flag offensive delete link more

answered 2015-01-16 23:45:31 +0200

Lupp gravatar image

updated 2015-01-16 23:48:04 +0200

'Edit' > 'Find & Replace' (with "Other Options", 'Regular expressions' checked)


in 'Search for'


in 'Replace with'

'Replace All' should do. It will not distinguish between upper and lower case. If "Oberhannoverschwaidhaus" contained in the cell's text should also be accepted (not only cell contents with hannover as a delimited word) simply omit the two occurrences of "\b" which is matching any word border (including start and end of content). If you only abolish the second "\b" a cell containing "FC Hannoveringen" will also be changed to "Niedersachsen".

Schöne Grüße aus München

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2015-01-16 21:27:38 +0200

Seen: 1,388 times

Last updated: Jan 25 '15