How do you introduce a CR and LF into a cell so when it is saved as a Tab delimited text file, the importing program will recognize the CR and LF?
4 Answers
You must use CRLF at the end of every row, and LF for carriage return inside a cell
In this way a spreadsheet will read properly the input
You can use the ASCII character codes in the cell:
Line Feed =CHAR(10)
Carriage Return =CHAR(13)
If need be, copy and paste values to get rid of the formula.
I use NotePad++ to edit the file after export.
View > Show Symbol > Show End of Line.
Then highlight the line break characters and do a find/replace (CTRL + h). [FYI: It only works for me when I highlight and not when I used the extended characters.]
In the 'Search Mode' section (bottom left of find/replace window) choose Extended.
Then in the 'Replace With' box put in what end-of-line character you want:
\r = Carriage Return = CR
\n = Line Feed = LF
\r\n = CRLF
For my needs, I replace CRLF with \n and then I replace LF with \r\n. I have to do it in this order otherwise if I replace the LF first I get a CRCRLF; you may need to consider your order.
I'm trying to avoid this process with this question to make a batch file that does it for me here: https://stackoverflow.com/questions/73225619/batch-script-to-replace-crlf-with-cr-then-lf-with-crlf-excel-to-timeslips-impo
Please upvote the question if it would help you.
Late to the party here, but if the goal is to introduce linebreaks into the original Excel file in such a way that they survive export to CVS and import back into Excel, use ALT+ENTER to add the linebreaks where required in the cell itself.
This automatically gives you the same result in the exported CSV as @aborruso has described.