How to find and replace line breaks within a Calc cell

[rpmelvin]

I have a huge, 6,000+ row Calc spreadsheet of my Great-Grandfather’s diaries that has text within cells that are formatted with line breaks. Here is an example from one of the cells:

Tuesday April 16, 1861 <line break here that I want to replace with a “br” html tag
…Studying also about the constitutionality of coercing the seceding states, preparing for a debate.
<line break here that I want to replace with two “br” html tags
Thursday April 18, 1861Tuesday April 16, 1861 <line break here that I want to replace with a “br” html tag
…Went to town after four o’clock to see the military, but was disappointed. Thought considerable about the war.
<line break here that I want to replace with two “br” html tags
Saturday April 20, 1861Tuesday April 16, 1861 <line break here that I want to replace with a “br” html tag
…went to town and saw the soldiers march and got me a pair of boots for which I paid $5.00. Great excitement in town about the war. Harpers Ferry destroyed.

This spreadsheet will ultimately be converted to an html table for use in a special project.

In addition, when I select one of these cells, all that appears in the edit box at the top of the screen is the first line of the cell. None of the other text shows there. Consequently FIND & REPLACE cannot find the line breaks.

What can I do to fix this?

[Lupp]

@rpmelvin

Consequently FIND & REPLACE cannot find the line breaks.

F&R isn’t for use in the formula bar (edit box?).
It searches the sheets’ cells.
BTW: There is a triangle icon with tips at the right end of the Formula bar.
Line breaks inside cell texts are represented by CHAR(10) and can be found per REGEX search using the reserved \n .
I think they should be replaced with a space to get what you want. See example:
disask_135435_LineBreaksInCells.ods (30.7 KB)

[rpmelvin]

Thanks, Lupp! I didn’t mean to imply that I was searching in the Formula Bar. I am searching using the cells. And I now see the triangle icon in the formula bar that I had never noticed before. Good tip!

I have attempted to use the “\n” in a REGEX search, but it only finds the FIRST instance of a line break within a cell. It does not see any others that are in the same cell. In my original post, all three of those dated entries are in the same cell, but the \n search only finds the one immediately following the “Tuesday April 16, 1861” date on the first line.

[Lupp]

@rpmelvin

...but it only finds the FIRST instance of a line break within a cell. It does not see any others that are in the same cell.

This isn’t the case for me.
What version of LibO do you use?
Provide a somehow realistic example in a CALC (.ods) file.
It should show what you have, and what you want to get.
I won’t make an extra example from the text you posted.
(And I’m not an expert in HTML.)

[schiavinatto]

@rpmelvin

I have attempted to use the “\n” in a REGEX search, but it only finds the FIRST instance of a line break within a cell.

Select the desired column and use: Ctrl + H
(1) \n
(2) “a space”
(3) Values
(4) Replace all.

[CRDF]

@rpmelvin

I have attempted to use the “\n” in a REGEX search, but it only finds the FIRST instance of a line break within a cell. It does not see any others that are in the same cell.

Indeed
But it replaces them all.
Find and Replace…
Regular expressions
Find:\n
Replace:<br>
Replace All

 
LO: 24.2.7.2 (X86_64) / LibreOffice Community
OS: Linux 6.17; UI render: default; VCL: gtk3
Locale: pt-BR (pt_BR.UTF-8); UI: en-US
Ubuntu package version: 4:24.2.7-0ubuntu0.24.04.5

[rpmelvin]

PROBLEM SOLVED!

In reading the responses from you folks, I saw that all of you were successful in doing what I wanted to do. In the process of doing my “digital detective work” I have discovered an anomaly in the Calc “Find and Replace” function.

I was in a “testing” mode when beginning this work, so I handled things bit differently than in your examples. I highlighted a small group of cells and entered the search and replace criteria exactly as schiavinatto showed in his reply. However, I did NOT use “Replace All” at this point. I used the “Find Next” and “Replace” commands, to do them one by one, because at this point I was just testing this process. Using this method, Calc did not find all the line breaks. It only found and fixed the first instance within a cell.

After reading your replies, I decided to set up the Find and Replace search again, only this time I chose “Replace All.” IT WORKED! All of the html the code I expected to see was properly placed in the cell. I had been trying to solve this problem for a couple of weeks, to no avail. I never thought that the Find and Replace behavior would be different between Find Next/Replace and Replace All.

A HUGE thank all of you who responded to my question.

[LeroyG]

@rpmelvin

I used the “Find Next” and “Replace”

It works if do you “Find Next” and “Replace” once for each instance.
Notice that you don’t need to “Find Next”: “Replace” is enough.
“Replace All” is faster.

Tested with:

Version: 25.8.7.3 (X86_64)

Build ID: 30742500f2d3eb4366ac312fa33d3dcabdb3eba5
CPU threads: 12; OS: Windows 10 X86_64 (build 17763); UI render: Skia/Raster; VCL: win
Locale: es-MX (es_AR); UI: en-US
Calc: threaded

[rpmelvin]

I was not clear enough in my response above.

I did what you suggested. After clicking on “Find Next” and clicking on “Replace” the app only replaced the first instance of a line break in the cell. Clicking “Replace” again added my replacement text to the same line break. It did not see or find the next instance in the cell.

Using “Replace All” found and fixed ALL the line breaks, including multiple instances within the same cell.

[LeroyG]

@rpmelvin

Clicking “Replace” again added my replacement text to the same line break

That is not the normal behavior. Can you retest after Restarting in Troubleshoot Mode (ex Safe Mode)?