separate multiline address into separate columns

[-Dman100-]

I have an excel file where I need to manipulate the billing street and
shipping street fields.

The billing and shipping street field might have two or three lines...i.e.:

Archdiocese Of Newark School
2 Cedar Street

or

Kokomo Center Township C.S.D.
100 W. Lincoln Road
P.O. Box 2188

I want to separate each line into its own column.

Some of the rows have empty columns for the address.

I tried running a macro using the following subroutine:

Sub SplitThem()

Dim LastR As Long
Dim Counter As Long
Dim arr As Variant
Dim arr2 As Variant

LastR = Cells(Rows.Count, 1).End(xlUp).Row
arr = [a1].Resize(LastR, 1).Value

Worksheets.Add

For Counter = 1 To LastR
arr2 = Split(arr(Counter, 1), Chr(10))
Cells(Counter, 1).Resize(1, UBound(arr2) + 1).Value = arr2
Next

End Sub

I get an error that occurs on row 7004, which is thrown on the following
line when I run the macro:

Cells(Counter, 1).Resize(1, UBound(arr2) + 1).Value = arr2

Run-time Error '1004':
Application-defined or object-defined error

The macro worked perfectly until row 7004, then it threw the error because
the next row was blank.

I copied all the contents of the billing street into a new worksheet and ran
the macro, which parsed the data out into three new fields up to row 7004,
then it bombed.

Thanks in advance for any help.

 

[Sheeloo]

Try
Sub SplitThem()

Dim LastR As Long
Dim Counter As Long
Dim arr As Variant
Dim arr2 As Variant
LastR = Cells(Rows.Count, 1).End(xlUp).Row
arr = [a1].Resize(LastR, 1).Value
Worksheets.Add
For Counter = 1 To LastR
'Added to check for empty row
If arr(Counter, 1) = "" Then
Else
arr2 = Split(arr(Counter, 1), Chr(10))
Cells(Counter, 1).Resize(1, UBound(arr2) + 1).Value = arr2
End If
Next
End Sub

 

[Ron Rosenfeld]

I have an excel file where I need to manipulate the billing street and
shipping street fields.

The billing and shipping street field might have two or three lines...i.e.:

Archdiocese Of Newark School
2 Cedar Street

or

Kokomo Center Township C.S.D.
100 W. Lincoln Road
P.O. Box 2188

You can use the Data/Text to columns wizard.

Select your column of Data
Data/Text to Columns
Delimited
Next
At this stage, under the Delimiters column, check Other. Then, with your
cursor in the empty box next to other, hold down the <alt> key while typing 010
on the numeric keypad. (Or type <ctrl-J> which is the equivalent).

Finish
--ron

 

[-Dman100-]

Thanks, that got it working. I got this subroutine off the web. If you
don't mind, can you explain what the following lines are doing in this
routine?



Sub SplitThem()

Dim LastR As Long 'These define the variables with the associated
datatypes...why long and variant?
Dim Counter As Long
Dim arr As Variant
Dim arr2 As Variant

LastR = Cells(Rows.Count, 1).End(xlUp).Row 'What are these two lines
doing? It appears LastR is getting the total number of rows?
arr = [a1].Resize(LastR, 1).Value 'Not sure
what is happening here

Worksheets.Add 'Add a new worksheet

For Counter = 1 To LastR 'Looping thru the total number
of rows
'Added to check for empty row
If arr(Counter, 1) = "" Then 'Check if empty row
Else
arr2 = Split(arr(Counter, 1), Chr(10)) 'Splitting the data by line
break?
Cells(Counter, 1).Resize(1, UBound(arr2) + 1).Value = arr2 'Not
sure what this line is doing?
End If
Next

End Sub

Thanks.

Try
Sub SplitThem()

Dim LastR As Long
Dim Counter As Long
Dim arr As Variant
Dim arr2 As Variant
LastR = Cells(Rows.Count, 1).End(xlUp).Row
arr = [a1].Resize(LastR, 1).Value
Worksheets.Add
For Counter = 1 To LastR
'Added to check for empty row
If arr(Counter, 1) = "" Then
Else
arr2 = Split(arr(Counter, 1), Chr(10))
Cells(Counter, 1).Resize(1, UBound(arr2) + 1).Value = arr2
End If
Next
End Sub

I have an excel file where I need to manipulate the billing street and
shipping street fields.

The billing and shipping street field might have two or three
lines...i.e.:

Archdiocese Of Newark School
2 Cedar Street

or

Kokomo Center Township C.S.D.
100 W. Lincoln Road
P.O. Box 2188

I want to separate each line into its own column.

Some of the rows have empty columns for the address.

I tried running a macro using the following subroutine:

Sub SplitThem()

Dim LastR As Long
Dim Counter As Long
Dim arr As Variant
Dim arr2 As Variant

LastR = Cells(Rows.Count, 1).End(xlUp).Row
arr = [a1].Resize(LastR, 1).Value

Worksheets.Add

For Counter = 1 To LastR
arr2 = Split(arr(Counter, 1), Chr(10))
Cells(Counter, 1).Resize(1, UBound(arr2) + 1).Value = arr2
Next

End Sub

I get an error that occurs on row 7004, which is thrown on the following
line when I run the macro:

Cells(Counter, 1).Resize(1, UBound(arr2) + 1).Value = arr2

Run-time Error '1004':
Application-defined or object-defined error

The macro worked perfectly until row 7004, then it threw the error
because
the next row was blank.

I copied all the contents of the billing street into a new worksheet and
ran
the macro, which parsed the data out into three new fields up to row
7004,
then it bombed.

Thanks in advance for any help.