Removing carriage returns in middle of cell

Removing carriage returns in middle of cell - Access 2000

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

gtvarghese - 17 Nov 2004 18:49 GMT

I am trying to export an Access 2000 table to a comma-delimited text file.
The data originally came into Access thru various Excel files. I am guessing
that Alt-Enter was used in Excel to have multiple lines in one cell. This
action is brought into Access during the import and shows up in the exported
text file. As a result, a given Access record is separated into multiple
lines. How do I remove this "carriage return" so that the record stays on
one line?

Reply to this Message

Ken Snell [MVP] - 17 Nov 2004 19:08 GMT

Use a query to export the data from the table. Put all fields on the query
except the one you want to modify. In its place, use a calculated field that
modifies the data for output.

If your ACCESS 2000 is fully patched, you can use a calculated field similar
to this to replace the CrLf characters with an empty space:

OutField: Replace([Fieldname], Chr(13) & Chr(10), " ", 1, -1, 1)

Signature

Ken Snell
<MS ACCESS MVP>

> I am trying to export an Access 2000 table to a comma-delimited text file.
> The data originally came into Access thru various Excel files. I am guessing
[quoted text clipped - 3 lines]
> lines. How do I remove this "carriage return" so that the record stays on
> one line?

Reply to this Message

gtvarghese - 19 Nov 2004 18:13 GMT

Thanks Ken, but apparently I made a mistake. It seems the symbol I am
encountering is not a carriage return although it does start another line
when imported into a text file.
The symbol looks like the following: .
How do you represent this symbol?

George

> Use a query to export the data from the table. Put all fields on the query
> except the one you want to modify. In its place, use a calculated field that
[quoted text clipped - 14 lines]
> > lines. How do I remove this "carriage return" so that the record stays on
> > one line?

Reply to this Message

Ken Snell [MVP] - 19 Nov 2004 19:03 GMT

Tht symbol by itself does not tell you what it is. It's a "generic"
representation of any character that cannot be displayed. You have to find
out what it is in order to be able to extract/remove/replace it.

If it causes a new line in a text file, then it likely is the line feed
character, Chr(10). This also is the character that Excel uses to make a new
line in a cell.

Signature

Ken Snell
<MS ACCESS MVP>

> Thanks Ken, but apparently I made a mistake. It seems the symbol I am
> encountering is not a carriage return although it does start another line
[quoted text clipped - 22 lines]
> > > lines. How do I remove this "carriage return" so that the record stays on
> > > one line?

Reply to this Message

gtvarghese - 22 Nov 2004 18:47 GMT

Thanks Ken. You have been very helpful.

> Tht symbol by itself does not tell you what it is. It's a "generic"
> representation of any character that cannot be displayed. You have to find
[quoted text clipped - 37 lines]
> stays on
> > > > one line?

Reply to this Message