extracting data within cell where there is carriage return

[mart10]

I am using Excel 2013. I want to extract address data held in a cell into multiple cells depending upon no of cariage returns

eg I have in one cell an address in format :

Eindsestraat 133
5105 NA Dongen
The Netherlands

I want to show Eindsestraat 133 in address1 cell, 5105 NA Dongen in address2 cell and The Netherlands in address3 cell

Some addresses may go onto 4 or even 5 rows

Hope you understand what i want?

 

[combo]

Assuming your text is in A1:
1) use B as helper column, formula: =SUBSTITUTE(A1,CHAR(10),"|")
Use other unique character instead of "|" if necessary, copy down if you have more rows.
2) use C as helper columb, copy text from col. B to C,
3) apply text to columns for col. C, "|" as custom delimiter, D1 as destination.

combo

 

[zelgar]

You could also create a custom Function that has the following code:

Function addr(x As String, y As Integer)
Dim c
c = Split(x, Chr(10))
addr = c(y - 1)
End Function

To get the 1st line of data you would use the function =addr(C1,1), 2nd line =addr(C1,2), 3rd line = addr(C1,3)

If you want a macro to split everything, the following will work on the data in the current selected cell:

Sub addr_split()
Dim row As Integer, col As Integer, y As Integer
x = ActiveCell.Value
row = ActiveCell.row
col = ActiveCell.Column
Dim c
c = Split(x, Chr(10))
For y = 1 To UBound(c) + 1
    Cells(row + y, col) = c(y - 1)
Next y
End Sub

 

[mintjulep]

Or Combo's approach could be used with Find and Replace.