Hi
Is there any way in Calc to find a text within a cell? Some documents have lots of paragraphs (unfortunately) inside each cell. Find utility only shows me that there is a piece of text in a cell, but I hoped that it will also highlight exactly each occurrence of that text.
If it’s not possible in “vanilla” calc perhaps someone could recommend a plugin/extension? Or is it something we could just script ourselves?
Calc is an arithmetic calculator in the first place.
It is not a tabular text editor nor database.
I know, sorry, some users often use it as a spreadsheet for all kinds of information tracking. I can’t control that sadly. It’s possible to convert .xlsx file to a pdf and search that instead, but formatting sometimes is a bit off and it would be easier/more reliable to search directly in calc.
Hi Daniel and welcome!
Okay, let’s say you found all occurrences of the word in the cell texts. What do you want to do with them?
I just set up a simple experiment.
Filled a dozen cells with dummy text
Selected them all and copied
Created an empty text document by Writer and pasted
Pressed Ctrl+H and searched for all occurrences of the word “he”.
Closed the Search%Replace window, Bold clicked.
Now I selected the entire text table (twice Ctrl+A), copied it, returned to Calc and pasted it. Yes, all instances of “he” are marked in bold.
Hi and thanks for asking. Just highlighting them in some way would do (changing color, changing background, making the font bold). You could then use calc’s regular “find” utility to jump between them. The purpose is “read-only”. Ideally this would be done without leaving calc. There might be also lots of cells, so copying and pasting them between programs could get slow over time.
Hopefully it’s something a scripts in calc could provide.
In other words, do you need a convenient means of navigating between all instances of a particular word?
Yes, exactly. Sorry if I haven’t explained it well. It’s only meant to be a “quality-of-life” improvement.
I would appreciate a sample data. You know, the most difficult part of writing a script is preparing the test data according to their verbal description: you will definitely miss some detail that you were not told about.
This is a better example including an .odf spreadsheet file.
Spreadsheet contains three cells with a word “dinosaur” somewhere. I’m looking for a script that could highlight occurrences of this word within these cells (and not just highlight the cells themselves):
Thanks for the sample, that’s enough.
This will take some time - I need to think about the design of the user interface. The task itself “find and select” should not be difficult. But since we are talking about a “quality-of-life” improvement, then the use of the script should be made simple.
No worries and thank you. Really appreciate that. Are you contributing by any chance to libre office source code? It would be amazing to have this kind of features natively in the program for example as an option to the find utility “highlight in cell” or even by default.
The CurrentController of a spreadsheet document has no means to select parts of cell-contained texts, imo. (Same with shapes.)
Considering the general situation I see one fork and some options.
More attractive to me would be a fundamentally self-made solution using a tiny helper model of TextDocument type. We could export the contents of pre-found cells one by one to that object, find anew the searched text, now as elements of a TextRanges object and thus selectable, make changes, and transfer back.
A medium attempt might use a split of cell texts using the REGEX() function. No visual selection then, of course.
We could export the contents of pre-found cells one by one to that object, find anew the searched text, now as elements of a TextRanges object and thus selectable, make changes, and transfer back.
That sounds good to me.
We be of one blood, thou and i 
I was thinking about exactly this approach (well, with slight differences - without returning the marked-up text to the cell, so as not to spoil the original data: just a floating textbox on top of the next cell with a copy of the text and marked instances of the search string)
Well, “transfer back conditionally”.
without returning the marked-up text to the cell, so as not to spoil the original data: just a floating textbox
Yes, that would be better I think. Thanks for explaining your thought process @JohnSUN @Lupp. I did not expect all this complexity and really appreciate your help.
Almost there:
search_string.odb (20.2 KB)
Open the form, enter search string, hit Enter.
The grid shows records having matches.
You can navigate through the records having matches.
Button [ → Select] selects the matching occurrances one by one in the multi-line text box.
thank you very much, sorry, but I am new to the “Base”. Is that correct that in order to search through a given calc’s table one would need to load it through the search_string.odb first?
There is no spreadsheet at all. I pasted your text into the database form. Species name into the first column, descriptions into the “memo” column.
Sorry, I might not have explained my problem properly, but I was looking for a way to highlight occurrences of some text directly in Calc. The use case was going to be reviewing numerous .xlsx documents on ad-hoc basis. Thank you for showing me that this can be done in another way, but I think that transferring data between programs will require too many steps for this particular use case.
