Multi line cell content extraction

[ahmedvolks]

Hi community,
I have a 5000 rows in Libreoffice calc file and the data have issue, It is a multi line cell So I need the extracted data to be in one line separated with commas in the next cell.

Below a cell of the file:

EconomicsLaw: Contract(s)
Law: Patents, Trademarks, CopyrightCertificates, Diplomas, Licenses, CVs
Law (general)Finance (general)
Business/Commerce (general)

Also I need the words like EconomicsLaw to be corrected, Any one have an idea?

My question is finished.

Note: While searching, I found a file from a respected person but it was corrupted. But I selected the full sheet cells and pasted as html 111111.xlsx . If I can increase the results as I need, This would be great.

The corrupted file is here :13919944284536718.ods

[mikekaganski]

Hi @ahmedvolks. Why posting as Wiki? It confuses things and helps no one.

[ahmedvolks]

Current cell as below:
“EconomicsLaw: Contract(s)
Law: Patents, Trademarks, CopyrightCertificates, Diplomas, Licenses, CVs
Law (general)Finance (general)
Business/Commerce (general)”

I made :
=SUBSTITUTE(A1,CHAR(10),CHAR(13))

Result in one line is:
EconomicsLaw: Contract(s) Law: Patents, Trademarks, CopyrightCertificates, Diplomas, Licenses, CVs Law (general)Finance (general) Business/Commerce (general)

Anyone can help only to correct the words like EconomicsLaw and CopyrightCertificates automatically without side effects?

[mikekaganski]

I’d first replace the newline in cells with some single character not used in other places (e.g., tab: use regular expressions in Find&Replace; replace \n with \t).

Then I’d select the cells and use Data-Text to Columns... feature with respective separator.

Correcting some words with different words is also easy with usual Find&Replace (Ctrl+F).

[mariosv]

I think I did the formula in your file.

Attached your file working, only remember that array in D4 doesn-t adapt their lenght automatically if the number of data to split change, so select the array with [Ctrl+/] and modify it dragging by their last cell.

Splitting cells

And please don’t use answers for comment, and as wiki doesn’t help neither.