Address in cell has Alt + return in it

[Alphacsulb]

I got some address from a customer that are all in a cell. It seems as though they Alt + Enter the address for some reason. I would like to get them in the following format

Excel Workbook
	C
6	Bellingham Marine1205 Business Park DriveDixon, CA 95620ATTN: Eric Noegel
7	Bob Leslie Yacht Broker13755 Fiji Way, Ste. D-5Marina del Rey, CA 90292ATTN: Robert Leslie, President
8	Brown, Winfield & Canzoneri300 S. Grand Ave, Suite # 1500Los Angeles, CA 90071ATTN: Geoffrey Mitchell
9	C B Richard Ellis355 S. Grand Ave., Ste. 1200Los Angeles, CA 90071
10	California Coastal CommissionSouth Coast Area Office200 Oceangate, Suite 1000Long Beach, CA 90802-4302ATTN: Al Padilla
11	Challenges Foundation732 N Martel AveLos Angeles, CA 90046ATTN: Nicolas Coster
Problem

This is what result should look like:

Excel Workbook
	D	E	F	G
6	Bellingham Marine	1205 Business Park Drive	Dixon, CA 95620	ATTN: Eric Noegel
7	Bob Leslie Yacht Broker	13755 Fiji Way, Ste. D-5	Marina del Rey, CA 90292	ATTN: Robert Leslie, President
Solution

I'm sure someone's already gone through this and can give me some guidance. Normally I use formulas like this to get the job done, but I'm stuck on this:

<table border="0" cellpadding="0" cellspacing="0" width="415"><col style="width: 311pt;" width="415"> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 311pt;" width="415" height="17">This finds the 1st " " and gets everything to the left of that.</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">"=LEFT(A1,FIND(" ",A1)-1)"</td> </tr> </table>

<table border="0" cellpadding="0" cellspacing="0" width="405"><col style="width: 304pt;" width="405"> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 304pt;" width="405" height="17">This finds the 1st " " and gets everything to that right of that.</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">"=RIGHT(A1,LEN(A1)-FIND(" ",A1))"</td> </tr> </table>
<table border="0" cellpadding="0" cellspacing="0" width="250"><col style="width: 188pt;" width="250"> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 188pt;" width="250" height="17">This finds the 1st " " and gets everything to that right of that.</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">"=MID(A1,1+FIND(" ",A1),100)"</td> </tr> </table>
Finds 1st number, and puts everything to the left of it.

<table border="0" cellpadding="0" cellspacing="0" width="405"><col style="width: 304pt;" width="405"> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 304pt;" width="405" height="17">"=LEFT(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&"1234567890"))-1)"</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> </tr> </table>

 

[VoG]

Try selecting those addresses then running

Sub Splt()
Dim c As Range, X
For Each c In Selection
    With c
        X = Split(.Value, Chr(10))
        .Offset(, 1).Resize(, UBound(X) + 1).Value = X
    End With
Next c
End Sub

 

[Alphacsulb]

That's great! Worked like a charm. Thanks a lot.