Concatenate and keep style

[trentmu]

I have 2 strings which I concatenate using a marker/delimiter ("^^") so that I can do a substitute of ^^->char(10)

• Text1
• Text2
  which becomes
  Text1^^Text2
  and then a multiline cell with
  Text1
  Text2

What I would like is the create
Text1
Text2
with the Text2 in red as well.

I did try formatting the source cells Text1(plain) and Text2(Bold/Red) in the hope that those styles would be retained, but no luck just yet.
I can get the conditional formatting to make the entire cell bold/red, but that doesn’t seem to work on partial cell content.

If this isn’t possible, can one do a manual search/replace of Text1\nText2 to Text1\nText2 in say an entire column?
Could it be done via a macro?

Any tips/ideas welcomed!
Thanks

[Lupp]

As long as you want to create the concatenated result by a formula there is definitely no way. Textual results of formulas always are simple strings getting the attributes assigned to the cell.

[trentmu]

OK - thank you for the reply. I will dabble around a bit and see if I can find another way - maybe I can mangle the XML version and then re-import to normal format

[karolus]

years ago I wrote pythoncode to highlight python-code inside Calc-cells, the runtime is worse enough: ~1min / 50loc.

from pygments import lex
from pygments.lexers import Python3Lexer as py3 
from pygments.styles.default import DefaultStyle

from com.sun.star.awt.FontSlant import ITALIC, NONE

style = DefaultStyle().styles

def getprops(keyval):
    
    color, slant, weight = -1, NONE, 100.0
    keyval = keyval.split('#')    
    if len(keyval)==2:
        color = int(keyval[1],16)
    attribut = keyval[0]
    if attribut and not attribut.startswith('no'):
        if 'bold' in attribut:
            weight = 150.0
        if 'italic' in attribut:
            slant = ITALIC
    return (color, weight, slant)

def write_format_token(cursor, string, props):
    
    propnames = ('CharColor','CharWeight', 'CharPosture')
    cursor.setPropertyValues(propnames, props)
    cursor.Text.insertString(cursor, '%s' % string, False) 
    
def highlight_code(*_):
    doc = XSCRIPTCONTEXT.getDocument()
    doc.addActionLock()
    sel = doc.CurrentSelection
    code = sel.String.strip()
    sel.String = ''
    cursor = sel.createTextCursor()
    cursor.CharFontName = 'Liberation Mono'
    loc = cursor.CharLocale
    loc.Language = 'zxx'
    loc.Country = ''
    loc.Variant =''
    cursor.CharLocale = loc
    formatdict = dict()
    for propkey, string in lex(code, py3()):
        if not propkey in formatdict:
            formatdict[propkey] = getprops(style[propkey])
        cursor = cursor.End
        write_format_token(cursor, string, formatdict[propkey])
    doc.removeActionLock()

and how it looks in calc:

[trentmu]

Thank you karolus, that looks very interesting. I am unfortunately not much use at coding and wouldn’t know how to implement such a script.

[karolus]

It was only proof of concept, Calc is not designed for that!

[Lupp]

@trentmu

I can get the conditional formatting to make the entire cell bold/red, but that doesn’t seem to work on partial cell content.

If this isn’t possible, can one do a manual search/replace of Text1\nText2 to Text1\nText2 in say an entire column?

Neither ConditionaFormatting nor FindReplace can format text ranges inside cells in Calc.

@trentmu

Could it be done via a macro?

It depends.

• You need to study macro programming for Calc rather profoundly.
• The processed cells can no longer contain a formula.

From my point of view your not mentioned final goal should be achieved without multiline cells. This may be another case of this well known problem.

On the shown level of understanding of Calc there should not be any tampering with the persistent (XML) representation.

[trentmu]

OK, thanks again. I will do the simple approach…ie. Copy cells and paste values …into another sheet, and then manually adjust the styling. The 1st option is a nice to have rather than a need to have, and there aren’t THAT many to do.

[trentmu]

Instead of doing the complex process above, I have succeeded by doing something like this post to add empty rows below the relevant cells - and then using a formula to add the required text into its own cell just below the multiline cell, and then using conditional cell formatting to pretty that up.

Add Empty Rows Below Multiple Selection in Libreoffice Calc

Using data in a format similar to the one below, how would I insert additional entire rows below all cells with blanks? 1 A 2 3 B 4 5 C I essentially want the following output after inserting 1 A 2 3 4 B 5 6 7 C 8 9 I can select blanks using regular expression “^$”, however, there is no option to insert rows. CTRL++ does not work.

[Lupp]

• For additional examples attach sheets,
• @johnSun didn’t create multiline cells, did he?

See also this example:
disask94688IntermittentPlacementUsingFormulas.ods (14.3 KB)