Tuesday, January 22, 2008

Inserting New lines in Excel Cells

Just a quick tip, if you ever need to output data to excel and it's more than a few thousand rows (which means you can't just spit out a html table and save it as an .xls file) you will encounter a problem with new line characters being shown as little squares.

I am talking about exporting from a real database ( ie Oracle ) to Excel via ODBC

You can't just output ASCII 10 or 13 or combinations thereof, Excel is just too dumb to understand! ALT-ENTER works if you edit cells by hand but if your outputting a mailing list for a mailing company who want the data in excel format with 40k rows that's no going to be an option.

Solution: Output you new lines as a character like '|' or '~' and then once the excel file has been created, Open it, Select your data and do a 'Replace All' using your chosen character. For the replacement character hold down ALT and type 0010 on the numeric keypad!

Excel will flash and grind like your working off an old generator running out of petrol using a 386SX but it will work.


Anonymous said...

This just saved me hours and hours of work.

Anonymous said...

br tag with style="mso-data-placement:same-cell;"

Unknown said...

This didn't work for me. When I try to replace with the 0010 code it just erases everything after my delimiter, including the delimiter.