Splitting Cells with Line Breaks

I was sent an Excel xls file where the Address column consists of up to 3 address lines stacked on top on each other - all in one cell. If there is more than one line in a cell, then all but the last line will have a square-cylinder looking character to the right of it. If you right-click the cell and select Format Cells, the Sample box has the lines separated by two square characters. If you click the Alignment tab and, in Text Control, uncheck the Wrap text, and click OK, then the lines combine into a single line in the cell, separated by the two square-characters. These characters don't appear to be copyable, so that if you do Data - Text to Columns - Delimited - Delimiter Other, it doesn't appear to work to paste the copied square character. Are these what they call "line breaks"? If so, how can they be gotten rid of, and the lines be put into separate columns? The reason for doing this is so they can be exported into a CSV or tab-delimited file.

This may or may not work......

in the text to columns > delimited dialog, click in the "other" option, and enter the delimiter character as ALT 010.

Good afternoon homersf

There have been a couple of posts along these lines this year. Do either of these two help :

http://www.excelforum.com/showthread.php?t=629978
http://www.excelforum.com/showthread.php?t=630826

HTH

DominicB

homersf,

Please read forum rules and message to cross posters below and then add the link to the other excel forum where you've posted the same question

VBA Noob