[Solved] Convert multi-line data in cell to single line

[Analise01]

Hi

Firstly apologies if this has been posted elsewhere - I tried searching first.

I have a calc spreadsheet with two to three columns of data. The first column has one line per cell, however the second and third columns have cells with both only one line of text, and multiple lines of text in each cell. I would like to rearrange the data in cells that have multiple lines of text to all be on the same line. As my original document has over 5,000 rows, doing this individually is not practical.

I have uploaded a picture of a dummy spreadsheet with an example of what the data looks like on the left, and how I would like it to be on the right.

I would appreciate any help. Thank you.

[karolus]

Hallo
use ⇒ Find and replace:
search:

Code: Select all

# search for
\n
#replace: 
<literal space>

[x]regular Expression

⇒⇒ replace all

[FJCC]

Try this on a copy of your document.
Open the Find & Replace dialog using the menu Edit -> Find & Replace.
In the Search For box, enter \n
In the Replace With box enter a space character
Click More Options and select the box labeled Regular Expressions
Optional - Click the Find All button an check that the cells you expect to change are selected
Click Replace All.

If that doesn't work for you, please upload a sample file, not an image, and explain what was wrong with the result of the above steps.

[Lupp]

My two cents:
1. Don't use whitespace again to separate the former lines. Use a visible separator like "/" for the purpose.
2. You can also create a second sheet, and there use a formula like

Code: Select all

=SUBSTITUTE(A1; CHAR(10); "/")

filled right and down to as many columns/rows as you need. The formula will adapt as needed due to the relative addressing.
CHAR(10) is the whitespace character used in Calc cells for "go to new line".

[Analise01]

Hi everyone, apologies for the late reply. Thank you all for your help - this has now worked on my document.