Ask Your Question
0

Multi line cell content extraction

asked 2018-04-08 10:29:19 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

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 C:\fakepath\111111.xlsx . If I can increase the results as I need, This would be great.

The corrupted file is here :C:\fakepath\13919944284536718.ods

edit retag flag offensive close merge delete

Comments

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

Mike Kaganski gravatar imageMike Kaganski ( 2018-04-08 10:59:35 +0200 )edit

3 Answers

Sort by » oldest newest most voted
0

answered 2018-04-08 10:35:28 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

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?

edit flag offensive delete link more
0

answered 2018-04-08 11:03:20 +0200

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).

edit flag offensive delete link more
0

answered 2018-04-08 15:35:21 +0200

m.a.riosv gravatar image

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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-04-08 10:29:19 +0200

Seen: 157 times

Last updated: Apr 08 '18