How to remove multiple carriage returns (line breaks) from cells in Calc

[ftrxx]

Hi,
I get speadsheets from my banking account with several CR LF in one cell which results in a varying number of spreadsheet lines for a single bank account movement. The cell content has to be “flattened” (i.e. the CR LF removed) to create a single line per movement so that I can use the downloaded spreadsheet for calculations and graphing.

A second difficulty is that a single account movement can have 2, 3, or more lines.

The tables looks like this.

I tried with the CLEAN functions but it cleans the first line of a cell only , not a cell with multiple lines.

This site
gives a solution for the problem that works in Excel but it does not in LibreOffice.

Manually removing did not work either as it seems that the Ctrl-J shortcut does not exist in Calc.

I then adapted the SUBSTITUTE function given in the Ablebits example to my table
=SUBSTITUTE(SUBSTITUTE(B3:B6;CHAR(13);"");CHAR(10);"")
but the functions only reads the first line, in this example B3.

Maybe manually retyping several hundreds lins of accounting would be more rapid ??
I can’t believe it.

Any working idea appreciated.

Libreoffice 5.4.2 64-bit, Win 7 Prof

[Lupp]

Strange!
-1- Who did fill the information into an .ods? The bank isn’t expected to do so.
-2- Images instead of working files is always annoying. Often misleading, rarely useful.
-3- As far as I would judge from the image, there is no multiline cell. Some cells are merged. A very bad idea.

First unmerge the sheet (a copy of it) completely to see the real structure.

Attach a simplified example.

[erAck]

From the picture it looks like the problem is not line feeds, but the cells with the date and the number are each merged over rows 25 to 29 to cover the same number of rows the invoice description (or whatever it is) is spread over. Likely the problem is already in the original file the bank hands over, but as we don’t know what that is it’s just a guess.

[ftrxx]

Here comes a data file as can be downloaded from the bank’s web site.Other formats available are slk-xls, text, cvs. All produce the same garbage.
The ods file is the format of my saved files.

My current “solution” is to print the ods file as a pdf, scan & ocr the print with FineReader and this makes the multiple lines per cas disappear. I look for a more direct way of getting the data: 1 account movement = 1 case = 1 line…

[erAck]

If that linked .xls file is the original then that is already garbage, ask your bank to provide a data processing friendly data structure.

You could use the CSV file and preprocess with awk or similar text processing language to combine description field content to one record and eliminate meaningless rows before importing it into the spreadsheet. Or write a BASIC or Python macro that does it in the spreadsheet.

[Lupp]

If your bank actually provides that garbage as the default online accounting information you should change the bank.
There is bad CSV style (mainly concerning the dates) used by USA banks as many requests already showed, but this is topping everything I saw to date.

[Lupp]

Very specific tasks of the kind may be solved best (concise, efficient) by general programming. It may seem strange, but you can use LibreOffice, in specific Calc, in a way not doing anything spreadsheets are supposed to do.
One sheet for input, one for output (or one for both these purposes) and a container for a user program. Not a single formula!
See this example (19 KiB).

The same task solved by proper spreadsheet means is more sensitive in some respects, needs a lot of complicated formulae, and may get inefficient if input is scaling up. Not a single line of user code!
See this example (180 KiB).

Both solutions cannot substitute a real banking solution. It’s very strange what a bank supposes to be accepted as an accounting report. I simply don’t understand. My bank does better, fortunately.