find and replacenewlinestext editing
This is a different from someone else's similarly-titled question. Neither Excel nor OpenOffice allow users to specify line delimiters, so I'm trying to replace semi-colons with line breaks. How do I edit the code of a text file?
In Notepad I tried replace ; with /n. I didn't think it would work; and it didn't. My long single-line txt/~csv file is crying: Help!
This is certainly not simple to explain... but after it is setup it will work with minimal effort. Also this is a slight variation on what @pnuts has outlined
I suggest using a template spreadsheet that you can copy-paste to. the template i have created has 3 tabs that look as follows.
Data tab is where the data is pastedLength is going to do some math to determine the longest columnSpace insert is going to insert the appropriate number of spaces (edit) you can copy from here and get the correct resultthe length tab looks like
Row 1 has maximum characters of the column below is and contains the formula
=MAX(A2:A101)+1
+1 creates the delimiter.
Row 2 through to n (which i have extended to 100 for this example) contain a formula to evaluate the length of each string
=LEN('Data tab'!A1)
the space insert tab looks like
Each cell contains a cell to evaluate the length of itself in comparison with the max value (+ delimiter) and insert an appropriate number of spaces.
='Data tab'!A1&REPT(" ",length!A$1-length!A2)
Note the $ which locks Row 1 if you copy and paste the formula
(edit) you can copy from the space insert tab.
Press Ctrl + H.
Use the following settings:
Find what: ; (with one trailing space)
Replace with: \r\n
Search Mode: Extended
Wrap around: checked
Click Replace All.
\r\n symbolizes a Windows newline, i.e., carriage return character followed by a linefeed character.
Press Ctrl + H.
Use the following settings:
Find what: ; (with one trailing space)
Replace with: ^l
Click Replace All.
Again, ^l symbolizes a Windows newline.
Best Answer
Using sed: