How do I change the color of only part of a text that is created by a formula

I have a formula that creates a text label: =Q1&" "&MID(V1,9,9)&RIGHT(V1,13). The label the formula creates is : Total (Savings) vs Last Year. I want the Mid(v1,9,9) to be displayed in red. It seems that whenever you use a formula to create text the original formatting is lost. I can display the entire text in Red or none of the text. I could convert the formula to straight text and then format the (Savings) part to Red, but I want to preserve the formula.

You can format some of the text displayed by the formula in a different color (you need a macro to do this), but the formula will be destroyed and converted to the text it was displaying.

1 Like

Thanks for the response, even though it was not what I was hoping to hear.

1 Like

To explain a little, LibreOffice uses an object (as in object-oriented programming) called a text cursor to change specific parts of a text string. The text cursor comes from the cell object itself, so to manipulate the text you have to assign that text to the cell first then manipulate the text with the cell’s text cursor. In fact, once you manipulate the text cursor, the reassignment will happen automatically. That assignment wipes out the original formula. It also seems to be an unstable situation if it is called as a User Defined Function…I tested it, and it led to Calc crashing on several attempts, although once recovered the text did, in fact, have two colors.

For those coming later who may have different specific needs, here is an example spreadsheet that has a macro that will take a cell, determine if the content needs to be colorized, then colorize that content based on fixed-length subparts of the content. This roughly matches the OP need, I think, but will convert all affected cells to text.

ColorResult.ods (12.9 KB)