csv carriage return don't show up in Excel

1
votes

I want to import a csv file in Excel. The csv file has carriage returns but Excel don't show them (see picture 1). The carriage return only comes up when i select the text in the cell and press enter.

Is there a way to solve this automatically? (vba)

  • I've tried to substitute the carriage return by a semicolon, but Excel does not find the carriage return. (=SUBSTITUTE(F2, CHAR(10), ";")
  • Text wrap is turned on in the cells
  • I tried to find the carriage return with a macro and the "instr()" function but Excel does not find the Chr(10).

Sub FindCarriageReturn()

myPos = InStr(1, Range("F2"), Chr(10))

End Sub

All documents: https://drive.google.com/open?id=1Kwr1VjCSdxtbtNcM6h5Rl8tc_Mo9XIA6

Thanks in advance!

Dieter

picture

1
excelcsvcarriage-return
Do you have text wrapping turned on for those cells? – Tim Williams
if i turn it on, there is no difference – dieter declerck
Can you upload a text (csv) file that demonstrates the problem (with sensitive information removed)? Upload to some sharing site (eg DropBox, OneDrive, etc) and post a link in your question. – Ron Rosenfeld
It is done. The link will give you the csv file, the excel-file with the problem and the excel-file with the desired result. If there is any problem with the link, please tell me. Thank you :) – dieter declerck

1 Answers

0
votes

Your CSV file contains a carriage return (CR, CHAR(13), Chr(13) or vbCr in VBA) within the data, and carriage return & line feed (CR LF, CHAR(10) & CHAR (13), vbCrLf in VBA) at the end of each line.

If you open the CSV file with e. g. notepad++, it looks like this:

CSV file with notepad++

As Excel needs a line feed within a cell to get visible text wrapping, you may use a formula like this:

=SUBSTITUTE(F2, CHAR(13), CHAR(10))