Need to replace character string with a line or paragraph break

[jcarls]

I cannot find the correct combination of inputs to replace an HTML break symbol with an embedded carriage return/line feed (or just a line feed, if that is all that is needed) to create paragraphs within a cell. The cell contains descriptive data exported from a database that allows CR/LFs within a text field, but cannot export that in a tab-delimited text file without causing incorrect line breaks. I am attaching a sample from the resulting spreadsheet.

When I try using various combinations of regular expressions in the help sections, I only get the visible replacement characters, but not the line breaks I need. If anyone has a specific solution for what to type into the “Replace” field, I would appreciate it.

[Grantler]

Linux:
SEARCH&REPLACE
Search: <BR>
Replace: CTRL+SHIFT+U and 000a + Enter
(Then: Replace All)

Windows? No idea. Or copy into Writer and use AltSearch.oxt.

[erAck]

Fwiw, in Writer it can be done by replacing with \n and regular expressions enabled. (which is not a regex replacement standard behaviour and isn’t implemented in Calc).
See also List of Regular Expressions and look for \n

[jcarls]

Yes, that confirms what I read (that it doesn’t work in Calc). When I tried “\n” I got “\n” (and, LOL, just trying to type that brings up some kind of secret function on this forum’s interface).

In Excel, you can (or could) type a Ctrl-J, but that doesn’t seem to work in Calc, either.

The easiest seems to be to add a SUBSTITUTE() formula in an adjacent column and use CHAR(10), but I was hoping for a solution users could use “in place.”

[fpy]

@jcarls

“in place.”

Find and replace with a carriage return in LibreCalc

It is simple by macro, but text in cells will stay in one line and I don’t know why Libre 7.3.2.2 Win10x64 replCR.ods (32.7 kB)