Access newline becoming _x000D_

Question

I'm trying to make a report in access derived from data I have in excel. So I import my excel sheet in access using the external data options, but my newlines keep appearing as "_x000D_"

As a example I use a excel sheet with 4 columns all with a title and all with 1 row of data containing in order from left to right:

="a"&char(10)&"b" 
="a"&char(13)&"b" 
="a"&char(10)&char(13)&"b" 
="a"&char(13)&char(10)&"b"

In my excel sheet I have tried changing the newlines to everything I could think of but the two that appeared to have done something are char(10) and char(13) however char(10) doesn't appear in access at all and char(13) appears to become "_x000D_"

Answer 1

See How to import from Excel and keep the line breaks :

Excel uses line-feed character (ASCII 10) as the line separator, while Access uses the combination of carriage-return + line-feed (ASCII 13 followed by ASCII 10) as the line separator.

After importing, you can use the Replace function to replace Chr(10) with Chr(13)+Chr(10). For example, you could execute a query like this:

UPDATE ImportedExcelTable SET MyField = Replace([MyField], Chr(10), Chr(13) & Chr(10));

So the only correct way to put a newline in an Excel cell is your first version:

="a"&char(10)&"b"

Then after importing the table, use the Update query to replace the Lf with Access newlines CrLf.