Trying to convert <br> to \n in Calc

[lduperval]

I have a LibreOffice sheet where many of the cells contain the string "<br>". I want to replace that string with a new line. So something like this:

1. some text
   2. some more text

becomes

1. some text
2. some more text

How do I do that? I tried using the Regular Expression field and it put the string "\n" in the cell, so I don’t think that’s the right way. I tried using alt+010 on the numpad but that doesn’t do anything.

EDIT: In case someone thinks of suggesting it, I did ask ChatGPT to do it for me and it worked. I would still like to know how to do it within Calc. if it’s possible. This the Python code it used:

import pandas as pd
import openpyxl
from odf.opendocument import load
from odf.table import Table, TableRow, TableCell
from odf.text import P

# Load the ODS file
file_path = '/mnt/data/BR-to-NL.ods'
doc = load(file_path)

# Function to replace "<br>" with newline
def replace_br_with_newline(cell):
    if cell:
        text_elements = cell.getElementsByType(P)
        for element in text_elements:
            text_content = element.firstChild
            if text_content and text_content.data:
                # Replace "<br>" with newline
                text_content.data = text_content.data.replace("<br>", "\n")
    return cell

# Process all tables in the document
for table in doc.spreadsheet.getElementsByType(Table):
    for row in table.getElementsByType(TableRow):
        for cell in row.getElementsByType(TableCell):
            replace_br_with_newline(cell)

# Save the modified file
modified_file_path = '/mnt/data/Modified_BR-to-NL.ods'
doc.save(modified_file_path)

modified_file_path

L
Text with BR.ods (10.3 KB)

[Zizi64]

Please upload an ODF type sample file here.

[lduperval]

Done. I saw this after I edited my post.

[karolus]

you do:

import pandas as pd
import openpyxl

but you dont use it!

[sokol92]

Select the range of cells containing <br> and run the macro:

Option Explicit

Sub Test
  Dim oRange As Object, oReplaceDesc As Object
  oRange=ThisComponent.CurrentSelection
  oReplaceDesc=oRange.createReplaceDescriptor
  With oReplaceDesc
     .setSearchString "<br>"
     .setReplaceString Chr(10)
      oRange.ReplaceAll oReplaceDesc
      oRange.Rows.OptimalHeight=True
  End With    
End Sub   

[ajlittoz]

Text such as the one you show in your sample file usually comes from some HTML page.

The simplest way to split this kind of text on <br> and spread it out in separate cells is imply to paste it (simple paste operation, not Paste as Unformatted). It will be considered an import by Calc and will be handled appropriately.

If you have lost the original text, copy the spreadsheet contents and paste it into a text editor (plain text). Save this text file with extension .html (not really necessary but play safe across all OSes). Open it wih a web browser, copy contents (where <br> is interpreted as a line break) and paste into Calc. Text is transferred into a new cell for every <br>.

If you want <br> to be a line break inside a cell, the procedure above does not work.

[lduperval]

Ah, that’s what I wanted. (BR to be converted to a line break inside the cell)

Well, I have a workaround by asking ChatGPT to do it for me and it gave me the result I wanted. So if there isn’t a better way, I’ll see if I can take the python code I put in my question and use it in a macro or something. Otherwise, I’ll ask the bot to do it again next time.

Thanks!

[EarnestAl]

@sokol92 has already given you a macro in a comment 5 hours ago.