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

[bennypr0fane]

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?

[LogicDaemon]

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)

[Lupp]

‘Edit’ > ‘Find & Replace’ (with “Other Options”, ‘Regular expressions’ checked)

.*\bHannover\b.*   

in ‘Search for’

Niedersachsen

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

[cikousek]

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 4.3.5.2 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.

[Lupp]

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.

[LogicDaemon]

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