Importing/Pasting text into Excel retaining Carriage Returns

Question

Post reply

Importing/Pasting text into Excel retaining Carriage Returns

NadJ

Ten Centuries

Points: 1031
More actions
December 8, 2004 at 12:08 pm

#60025

SQL / Excel question.
Basically I need to paste /import text into an Excel cell retaining carriage returns. In SQL I am abled to specify a special character for carriage returns 'CHAR(13)'. When displaying the results in text format I see my text formatted exactly how I want it to. When viewing the results in grid format, the CR's appear as spaces.
How can I prepare my data / or paste it into Excel so that the Carriage Returns are retained. Is there a special character I can include which will automatically tell Excel to start the next bit of text on another line?

Thanks again

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

you're already all set, just paste it in excel and change cell and the text will be presented as it should.

NadJ Ten Centuries Points: 1031 More actions · Answer 2

Except it's not

Actually small booboo on my part. I was pasting straight into a selected cell rather than the formula bar for that cell. Still have an issue though...

If I copy the data from text there is no way to get the other 'columns' to come with it (side by side) but you are correct the data is formatted correctly.

However, if I try copying the data from grid and paste it into the formula bar this way, Excel puts in a CR at the end of the last line only (pointless).

Thanks

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

just code the function to remove it (just check to see if it's 1 or 2 characters that need to be removed.. this removes the last 2 chars of each cell

Dim MyRange As Range

Dim MyCell As Range

Set MyRange = ActiveSheet.UsedRange

For Each MyCell In MyRange.Cells

MyCell.Value = Left(MyCell.Value, Len(MyCell) - 2)

Andy Robertson SSC Eights! Points: 935 More actions · Answer 4

Have you thought about creating a query table in excel?

NadJ Ten Centuries Points: 1031 More actions · Answer 5

Did that at first but got [] symbols instead. Turns out, all you do is format the cells to "wrap text"

All solved!

Thank you all.

Andy Robertson SSC Eights! Points: 935 More actions · Answer 6

Just for your info. If you want to put a carriage return in a cell while editing the cell hit alt+return.

Robert Stewart Hall of Fame Points: 3399 More actions · Answer 7

Carriage Return CHAR(13) is actually a hold over from the old line printers. In order to get a carriage return and a line feed, it takes 2 characters CHAR(13) + CHAR(10). Usually, you will get ugly little black boxes for this in Excel because this is from teh ASCII standard and not the ANSI standard. The key combination of Alt + Return is what Excel uses for a forced carriage return and line feed. Access uses the CTRL + Enter key combination.