How to replace the font colour of a partial CALC text

English
, , , , ,
#1

Is it possible to find and replace all instances of a particular text colour with an alternate colour?

The colour will only be applied to a random portion of the cell contents. Black black black RED black.

Only the red text colour should be replaced so any other colour is unchanged.

WRITER has a find & replace function that permits text format choices but that’s missing from the CALC find & replace.

Is there a possible regular expression that will achieve the objective?

Version: 7.0.5.2 (x64)
Build ID: 64390860c6cd0aca4beafafcfd84613dd9dfb63a
CPU threads: 4; OS: Windows 10.0 Build 19042; UI render: Skia/Raster; VCL: win
Locale: sv-SE (en_GB); UI: en-GB
Calc: threaded

#2

F&R with RegEx in Calc only looks for cells containing a match, but not into the texts in detail. If you are fond of programming with the LibreOffice API you can find ways. Lots of fun! For a simple related example see the attached file.
ask305195recolorTextPartsDemo.ods

Changer la couleur de certaines lettres d'un texte automatiquement
How does Calc colorize references in formulas? Is there an API method?
#3

Can you share your LibreOffice version? To do that, you can choose menu Help - About LibreOffice, copy from there, press edit below your question and paste.

#4

To @Lupp’s comment: Analysis of the code in the attached file.

For Each level1Part In crText  'level1Part -> com.sun.star.text.XTextContent
    For Each portion In level1Part  'portion -> com.sun.star.text.XTextRange

It is not entirely clear what “level1Part” is in this context? But it works interesting. If a text fragment is assigned a different font color, it is identified as a separate text range.

In fact, this is the solution to the OP problem.

#5

Hi @Lupp. You’ve lost me. I downloaded the file but can’t find anything resembling the code identified in @eeigor’s comment.

I simply allowed LO to open it - is that the wrong approach?

#6

It is not entirely clear what “level1Part” is in this context?

If a text is enumerated or analyzed using For Each there is a first level of enumeration which isn’t exactly treated. the same way in every case. In Writer this level returns the paragraphs which may contain hard line breaks (ASCII LF in the string). In a Calc cell it returns the lines (separated by ASCII LF now) while ASCII CR generally doesn’t occur. If forced into the string by the user, CR is seen as an alternative separator on the firts level, but disregarded for the rendering of the cell.
This mess was the reason for what I didn’t use the term/name para or similar in the code.
The second level should always be a TextRange, but also this service isn’t exatctly the same regarding the origin of the Text object it’s supported for.

#7

@WhiteKnight: The piece of code @eeigor quoted is found in the contained Module nonsenseGame as a fourth nested For-Next loop inside For Each rg … For c … For r
There comes a fifth nested loop For Each portion then.
Please note: To be able to run a document macro, the file must be loaded with the permission to run macros. You won’t be prompted for the permission if your macro security is set ‘High’, which is a good idea basically.
After having checked the contained code for absence of malign parts, you can temporarily set macro security to ‘Medium’ and reload the file with permission.

#8

@Lupp I think I need to change my avatar to “Dummy”. I’m only finding a spreadsheet on the link provided. Not sure what or where the module [nonsensegame] could be. From the spreadsheet and your above explanations, it appears to answer my question but I’m unable to investigate further as I can’t access the module.

#9

@lupp Sorry for adding to your burden, I clearly didn’t read all of your response so missed the part where I needed to allow macros. Like I said, Dummy;)

#10

Look at the code: >Tools>Macros>Edit
Run it (if permitted): >Tools>Macros>(Select the opened document, >Standard...)>OK
Change macro security: >Tools>Options>LibreOffice>Security>>Macro security>> (Never select Low!)

#11

It is not a good idea to colorize partially a cell content. The “basic” unit of the spreadsheets is the CELL. Format it consistently: with a cell style, what have one character color.

And never use the formatting properties as some “data”. They are NOT data.

If you want delete the different formatting property, the you can use the CTRL-M hotkey (delete ditect formatting properties). Then apply the desired cell style.

#12

Well, it may be not a good idea but sometimes it is very useful: when you have to update text sections in the cells to highlight changes without the record modification feature (that will merely highlight the whole cell and display the change, that tip not being always needed.

#13

Yes, but it is a temporary colorizing, and you will delete it (for example by a Ctrl-M) when you finish the document.

#14

Yes. Usually, you make the changes each week and select the whole sheet to reset the font color everywhere for the following update.

#15

It’s being utilised as a pseudo highlight - not data - which clearly identifies the point of interest far better than a full-colour background ever could. Works like a charm on a growing sheet with currently 1200+ text cells, where the specific highlight immediately catches the eye of the user. All the “data” cells have complementary conditional colour formatting which is just perfect for its defined purpose. If the LO rationale was to never partially colour text in a cell then it wouldn’t have been possible in the first place. Would you disagree?

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.