How to get rid of ALT + ENTER char (1 Viewer)

[accessfever]

Hi all,

I have an Access table with >100K records and it has a comment column which I joined from another small table. The small table has reviewers' comments by each part number they reveiewed which I copied and pasted from an Excel file. Somtimes, some reveiwers use the ALT+ ENTER key to separent two sentences in one comment cell so they can view their two sentences in two line easily.

I am trying to download the Access table in CSV file since the number of records is over 100K. When I open the CSV file, I notice there are 3 extra lines since there is a comment has the Alt + Enter char to separate two statements.

I wanted to get rid of the Alt+Enter char in the Access table's comment field. Is it a way to accomplish this?

 

[jdraw]

I think the characters within the comment field may be CR (carriage return) and LF (Line feed). There is an access intrinsic constant vbCrLf that you use when adding these characters to a field vale for formatting purposes.

You could try a small test to see if this in fact is the code involved.

Take 1 record (or a few, but not all 100k) and see if you can find the characters.

In the query grid, try

PosnOfChar: iif(Instr(yourCommentfield, vbCrLf)>0,"Found a Char","NotFound")

Can you post a few records of your csv?

 

[accessfever]

I used your codes to find vbCrLf in the comment column and the query returned PosnOfChar for all records as "Not Found"... So I think the vbCrLf is not the ALT+ENTER key in the comment field.

If you type two sentences in a cell of a spreadsheet, then press ALT+ENTER key at the end of first sentence. You should see the two sentences now in two lines in same cell. Then you copy this cell to an Access table. You should see that there is an "unknown char similar to space' at the end of the two sentences in the field when you highlight the field. I tried to manually delete the unknown char by Backspace key in the Access table but not worked......

Any idea to resolve this?

 

[jdraw]

I opened excel 2003, in a new spreadsheet I wrote a line of text followed by alt Enter, then a second line with alt Enter and then a third line.
I copied the cell contents to an editor, which I opened as binary.

I saw hex characters 0D 0A (13 and 10) at the end of line 1 and line 2.
I created a table in Access called excelToAccess

fields
Id autonumber
Fld memo

and copied the cell contents to field [fld]

I then ran a query
SELECT excelToAccess.id, excelToAccess.fld, InStr([fld],Chr(10) ) AS x
FROM excelToAccess;

After a little more testing and I think the Alt Enter is Chr(10)


My query shows 16 which represents the position of the first Chr(10).
When I tried to find the Chr(10) Chr(13) combination, it wasn't found.
So I'm fairly sure your Alt Enter is a Chr(10).

What do you want to do with it now?

 

[accessfever]

Awesome! I added a query to use Replace function to replace the chr(10) with "" string(Replace([COMMENT],Chr(10),"")) in the comment field before download the table in CSV format. When I open the CSV format in Excel, the data looks fine without those extra lines from the Comment field's 2nd sentences with ALT+ENTER key. Many thanks for your help!!!!