Wrapped Text-Delimiter

[jpxsjpxs]

Hi,
I exported some info into excel and I am having trouble manipulating it. I exported and address line into a cell. The cell automatically wraps the text and makes it into three lines in the cell. When I undue the wrap text I get one line but with a character square in between where the old line break was--which I think is called a delimiter. What I am trying to do is get rid of the sqaure and pull out the first two lines and put them into two new cells.
Here is what it looks like when I expoert to excel.

Jon Stevens
123 Main St
Anywhere, Pa 18000

I'd like to get the name and the address into two different cells. Thanks

Jon
<TABLE style="WIDTH: 213pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=284 border=0 x:str><COLGROUP><COL style="WIDTH: 213pt; mso-width-source: userset; mso-width-alt: 12117" width=284><TBODY><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 213pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" width=284 height=15></TD></TR></TBODY></TABLE>

 

[xenou]

Assuming your text is in a1, this will give you the first line:
=LEFT(A1,SEARCH(CHAR(10),A1,1)-1)

And this the rest:
=RIGHT(A1,LEN(A1)-SEARCH(CHAR(10),A1,1))

And to remove the square from the above, use substitute (we'll add a comma and space instead):
=SUBSTITUTE(RIGHT(A1,LEN(A1)-SEARCH(CHAR(10),A1,1)),CHAR(10),", ")


-----------------------------

Another idea that will work in a pinch is to:
1) copy the square from right out of the cell using your cursor
2) use Data | Text-to-Columns
3) for the delimiter, paste the square in

That should break it out into separate columns for each line. FYI the square is most likely a carriage return which is an unprintable character (one might think it would actually put a new line into the cell but apparently Excel doesn't "get it" here...for better or worse)