We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

How to replace the font colour of a partial CALC text

asked 2021-04-20 20:29:33 +0200

White Knight gravatar image

updated 2021-06-02 23:47:09 +0200

Alex Kemp gravatar image

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: (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

edit retag flag offensive close merge delete


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.

Lupp gravatar imageLupp ( 2021-04-20 23:28:35 +0200 )edit

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.

LeroyG gravatar imageLeroyG ( 2021-04-21 00:30:44 +0200 )edit

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.

eeigor gravatar imageeeigor ( 2021-04-21 07:30:34 +0200 )edit

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?

White Knight gravatar imageWhite Knight ( 2021-04-21 10:05:40 +0200 )edit

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.

Lupp gravatar imageLupp ( 2021-04-21 12:40:27 +0200 )edit

@White Knight: 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.

Lupp gravatar imageLupp ( 2021-04-21 12:50:36 +0200 )edit

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

White Knight gravatar imageWhite Knight ( 2021-04-21 13:09:31 +0200 )edit

@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;)

White Knight gravatar imageWhite Knight ( 2021-04-21 13:20:17 +0200 )edit

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!)

Lupp gravatar imageLupp ( 2021-04-21 13:21:08 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2021-04-20 20:46:53 +0200

Zizi64 gravatar image

updated 2021-04-20 20:48:51 +0200

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.

edit flag offensive delete link more


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.

Hagar Delest gravatar imageHagar Delest ( 2021-04-21 07:49:46 +0200 )edit

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

Zizi64 gravatar imageZizi64 ( 2021-04-21 08:10:00 +0200 )edit

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

Hagar Delest gravatar imageHagar Delest ( 2021-04-21 08:47:48 +0200 )edit

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?

White Knight gravatar imageWhite Knight ( 2021-04-21 10:01:39 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2021-04-20 20:29:33 +0200

Seen: 39 times

Last updated: Apr 21