how do i separate addresses and place results in another cell

M

maju

I am using excel 2003.
I will like to know how i can use the mid/left/right function to separate
the address I want column A&B to be separated by state, city, & zip. results
for column A should be right after colum A & results for coulm B should be
right after B.below is the data.
column A column B
Burlington, NJ 08016 San Francisco CA 94107
Braintree, MA 02184 San Francisco CA 94107
Pinebrook, NJ 07058 San Francisco CA 94107
Clifton Hts, PA 19018 San Francisco CA 94107
Sample of my code

Public Sub finalseparate_address()
Columns("b:b").Select
Columns("u:u").Select
Selection.Insert Shift:=xlToRight
Selection.NumberFormat = "General"
Selection.Insert Shift:=xlToRight
Selection.NumberFormat = "General"
Selection.Insert Shift:=xlToRight
Selection.NumberFormat = "General"
For Each addr In Range("a:a").SpecialCells(xlCellTypeConstants, 2)
addr.Offset(0, 1).FormulaR1C1 = "=LEFT(RC[-1], FIND("","",RC[-1],1)-1)"
addr.Offset(0, 2).FormulaR1C1 = "=MID(RC[-2],FIND("","",RC[-2],1)+3,2)"
addr.Offset(0, 3).FormulaR1C1 = "=MID(TRIM(RC[-3]),FIND("","",RC[-3],1)+5,12)"
For Each cell In Range("e:e").SpecialCells(xlCellTypeConstants, 2)
cell.Offset(0, 1).FormulaR1C1 = "=RIGHT(RC[-1]:R[17]C[-1],5)"
cell.Offset(0, 2).FormulaR1C1 = "=RIGHT(RC[-1]:R[17]C[-1],5)"
cell.Offset(0, 3).FormulaR1C1 = "=RIGHT(RC[-1]:R[17]C[-1],5)"

Next cell
Next addr
End Sub

thanks
 
M

merjet

It seems you could easily do this using the menu Data | Text to
Columns.

The following steps will suffice with the data you show. You may need
to modify them some if the data is not as uniform as you show.

First move the data in column B to D. Split A into 2 columns using
the
comma as delimiter. Then split B into 2 columns using a space
delimiter.
Split D into 4 columns using a space delimiter. Then merge D and E.

Hth,
Merjet
 
B

Bob Phillips

Public Sub finalseparate_address()
With Columns("B:D")

.Insert Shift:=xlToRight
.NumberFormat = "General"
End With

For Each addr In Range("A:A").SpecialCells(xlCellTypeConstants, 2)

addr.Offset(0, 1).FormulaR1C1 = "=LEFT(RC[-1],
FIND("","",RC[-1],1)-1)"
addr.Offset(0, 2).FormulaR1C1 =
"=MID(RC[-2],FIND("","",RC[-2],1)+3,2)"
addr.Offset(0, 3).FormulaR1C1 =
"=MID(RC[-3],FIND(""~"",SUBSTITUTE(RC[-3],"" "",""~""," & _
"LEN(RC[-3])-LEN(SUBSTITUTE(RC[-3],
"" "",""""))))+1,99)"
Next addr

For Each cell In Range("e:e").SpecialCells(xlCellTypeConstants, 2)

cell.Offset(0, 1).FormulaR1C1 =
"=TRIM(SUBSTITUTE(SUBSTITUTE(RC[-1],RC[2],""""),RC[1],""""))"
cell.Offset(0, 2).FormulaR1C1 =
"=TRIM(SUBSTITUTE(MID(RC[-2],FIND(""~""," & _
"SUBSTITUTE(RC[-2],""
"",""~"",LEN(RC[-2])-" & _
"LEN(SUBSTITUTE(RC[-2],""
"",""""))-1))+1,99),RC[1],""""))"
cell.Offset(0, 3).FormulaR1C1 =
"=MID(RC[-3],FIND(""~"",SUBSTITUTE(RC[-3],"" "",""~""," & _
"LEN(RC[-3])-LEN(SUBSTITUTE(RC[-3],
"" "",""""))))+1,99)"

Next cell
End Sub

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
M

Mike H.

Bob, when I do a simple Copy and paste of your code, I can't get it to work.
I tried cleaning up the carriage returns that get imbedded doing this, but I
can't get a clean copy of this code. For example, the first Offset(0,3) line
gives me a run-time error and the second For routine won't compile a single
line. Could you possibly give me ideas on what I am doing wrong?

Bob Phillips said:
Public Sub finalseparate_address()
With Columns("B:D")

.Insert Shift:=xlToRight
.NumberFormat = "General"
End With

For Each addr In Range("A:A").SpecialCells(xlCellTypeConstants, 2)

addr.Offset(0, 1).FormulaR1C1 = "=LEFT(RC[-1],
FIND("","",RC[-1],1)-1)"
addr.Offset(0, 2).FormulaR1C1 =
"=MID(RC[-2],FIND("","",RC[-2],1)+3,2)"
addr.Offset(0, 3).FormulaR1C1 =
"=MID(RC[-3],FIND(""~"",SUBSTITUTE(RC[-3],"" "",""~""," & _
"LEN(RC[-3])-LEN(SUBSTITUTE(RC[-3],
"" "",""""))))+1,99)"
Next addr

For Each cell In Range("e:e").SpecialCells(xlCellTypeConstants, 2)

cell.Offset(0, 1).FormulaR1C1 =
"=TRIM(SUBSTITUTE(SUBSTITUTE(RC[-1],RC[2],""""),RC[1],""""))"
cell.Offset(0, 2).FormulaR1C1 =
"=TRIM(SUBSTITUTE(MID(RC[-2],FIND(""~""," & _
"SUBSTITUTE(RC[-2],""
"",""~"",LEN(RC[-2])-" & _
"LEN(SUBSTITUTE(RC[-2],""
"",""""))-1))+1,99),RC[1],""""))"
cell.Offset(0, 3).FormulaR1C1 =
"=MID(RC[-3],FIND(""~"",SUBSTITUTE(RC[-3],"" "",""~""," & _
"LEN(RC[-3])-LEN(SUBSTITUTE(RC[-3],
"" "",""""))))+1,99)"

Next cell
End Sub

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



maju said:
I am using excel 2003.
I will like to know how i can use the mid/left/right function to separate
the address I want column A&B to be separated by state, city, & zip.
results
for column A should be right after colum A & results for coulm B should be
right after B.below is the data.
column A column B
Burlington, NJ 08016 San Francisco CA 94107
Braintree, MA 02184 San Francisco CA 94107
Pinebrook, NJ 07058 San Francisco CA 94107
Clifton Hts, PA 19018 San Francisco CA 94107
Sample of my code

Public Sub finalseparate_address()
Columns("b:b").Select
Columns("u:u").Select
Selection.Insert Shift:=xlToRight
Selection.NumberFormat = "General"
Selection.Insert Shift:=xlToRight
Selection.NumberFormat = "General"
Selection.Insert Shift:=xlToRight
Selection.NumberFormat = "General"
For Each addr In Range("a:a").SpecialCells(xlCellTypeConstants, 2)
addr.Offset(0, 1).FormulaR1C1 = "=LEFT(RC[-1], FIND("","",RC[-1],1)-1)"
addr.Offset(0, 2).FormulaR1C1 = "=MID(RC[-2],FIND("","",RC[-2],1)+3,2)"
addr.Offset(0, 3).FormulaR1C1 =
"=MID(TRIM(RC[-3]),FIND("","",RC[-3],1)+5,12)"
For Each cell In Range("e:e").SpecialCells(xlCellTypeConstants, 2)
cell.Offset(0, 1).FormulaR1C1 = "=RIGHT(RC[-1]:R[17]C[-1],5)"
cell.Offset(0, 2).FormulaR1C1 = "=RIGHT(RC[-1]:R[17]C[-1],5)"
cell.Offset(0, 3).FormulaR1C1 = "=RIGHT(RC[-1]:R[17]C[-1],5)"

Next cell
Next addr
End Sub

thanks
Click to expand...
Click to expand...
 
B

Bob Phillips

Crikey's Mike, it really made a mess of that didn't it.

The problem is that when it wraps a string over multiple lines, if it breaks
at an unclosed string, it will close it itself, so it inserts another quotes
("), so we are getting extra quotes. Coupled with spaces between quotes
getting trashed on a split (" " becomes ""), and it is a real mess.

This should work better.

Public Sub finalseparate_address()
Const Formula1 As String = _
"=LEFT(RC[-1],FIND("","",RC[-1],1)-1)"
Const Formula2 As String = _
"=MID(RC[-2],FIND("","",RC[-2],1)+3,2)"
Const Formula3 As String = _
"=MID(RC[-3],FIND(""~"",SUBSTITUTE(RC[-3],"" "",""~""," & _
"LEN(RC[-3])-LEN(SUBSTITUTE(RC[-3],"" "",""""))))+1,99)"
Const Formula4 As String = _
"=TRIM(SUBSTITUTE(SUBSTITUTE(RC[-1],RC[2],""""),RC[1],""""))"
Const Formula5 As String = _
"=TRIM(SUBSTITUTE(MID(RC[-2],FIND(""~""," & _
"SUBSTITUTE(RC[-2],"" "",""~"",LEN(RC[-2])-" & _
"LEN(SUBSTITUTE(RC[-2],"" "",""""))-1))+1,99),RC[1],""""))"
Const Formula6 As String = _
"=MID(RC[-3],FIND(""~"",SUBSTITUTE(RC[-3],"" "",""~""," & _
"LEN(RC[-3])-LEN(SUBSTITUTE(RC[-3],"" "",""""))))+1,99)"

With Columns("B:D")

.Insert Shift:=xlToRight
.NumberFormat = "General"
End With

For Each addr In Range("A:A").SpecialCells(xlCellTypeConstants, 2)

With addr

.Offset(0, 1).FormulaR1C1 = Formula1
.Offset(0, 2).FormulaR1C1 = Formula2
.Offset(0, 3).FormulaR1C1 = Formula3
End With
Next addr

For Each cell In Range("e:e").SpecialCells(xlCellTypeConstants, 2)

cell.Offset(0, 1).FormulaR1C1 = Formula4
cell.Offset(0, 2).FormulaR1C1 = Formula5
cell.Offset(0, 3).FormulaR1C1 = Formula6

Next cell
End Sub

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



Mike H. said:
Bob, when I do a simple Copy and paste of your code, I can't get it to
work.
I tried cleaning up the carriage returns that get imbedded doing this, but
I
can't get a clean copy of this code. For example, the first Offset(0,3)
line
gives me a run-time error and the second For routine won't compile a
single
line. Could you possibly give me ideas on what I am doing wrong?

Bob Phillips said:
Public Sub finalseparate_address()
With Columns("B:D")

.Insert Shift:=xlToRight
.NumberFormat = "General"
End With

For Each addr In Range("A:A").SpecialCells(xlCellTypeConstants, 2)

addr.Offset(0, 1).FormulaR1C1 = "=LEFT(RC[-1],
FIND("","",RC[-1],1)-1)"
addr.Offset(0, 2).FormulaR1C1 =
"=MID(RC[-2],FIND("","",RC[-2],1)+3,2)"
addr.Offset(0, 3).FormulaR1C1 =
"=MID(RC[-3],FIND(""~"",SUBSTITUTE(RC[-3],"" "",""~""," & _

"LEN(RC[-3])-LEN(SUBSTITUTE(RC[-3],
"" "",""""))))+1,99)"
Next addr

For Each cell In Range("e:e").SpecialCells(xlCellTypeConstants, 2)

cell.Offset(0, 1).FormulaR1C1 =
"=TRIM(SUBSTITUTE(SUBSTITUTE(RC[-1],RC[2],""""),RC[1],""""))"
cell.Offset(0, 2).FormulaR1C1 =
"=TRIM(SUBSTITUTE(MID(RC[-2],FIND(""~""," & _
"SUBSTITUTE(RC[-2],""
"",""~"",LEN(RC[-2])-" & _
"LEN(SUBSTITUTE(RC[-2],""
"",""""))-1))+1,99),RC[1],""""))"
cell.Offset(0, 3).FormulaR1C1 =
"=MID(RC[-3],FIND(""~"",SUBSTITUTE(RC[-3],"" "",""~""," & _

"LEN(RC[-3])-LEN(SUBSTITUTE(RC[-3],
"" "",""""))))+1,99)"

Next cell
End Sub

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



maju said:
I am using excel 2003.
I will like to know how i can use the mid/left/right function to
separate
the address I want column A&B to be separated by state, city, & zip.
results
for column A should be right after colum A & results for coulm B should
be
right after B.below is the data.
column A column B
Burlington, NJ 08016 San Francisco CA 94107
Braintree, MA 02184 San Francisco CA 94107
Pinebrook, NJ 07058 San Francisco CA 94107
Clifton Hts, PA 19018 San Francisco CA 94107
Sample of my code

Public Sub finalseparate_address()
Columns("b:b").Select
Columns("u:u").Select
Selection.Insert Shift:=xlToRight
Selection.NumberFormat = "General"
Selection.Insert Shift:=xlToRight
Selection.NumberFormat = "General"
Selection.Insert Shift:=xlToRight
Selection.NumberFormat = "General"
For Each addr In Range("a:a").SpecialCells(xlCellTypeConstants, 2)
addr.Offset(0, 1).FormulaR1C1 = "=LEFT(RC[-1], FIND("","",RC[-1],1)-1)"
addr.Offset(0, 2).FormulaR1C1 = "=MID(RC[-2],FIND("","",RC[-2],1)+3,2)"
addr.Offset(0, 3).FormulaR1C1 =
"=MID(TRIM(RC[-3]),FIND("","",RC[-3],1)+5,12)"
For Each cell In Range("e:e").SpecialCells(xlCellTypeConstants, 2)
cell.Offset(0, 1).FormulaR1C1 = "=RIGHT(RC[-1]:R[17]C[-1],5)"
cell.Offset(0, 2).FormulaR1C1 = "=RIGHT(RC[-1]:R[17]C[-1],5)"
cell.Offset(0, 3).FormulaR1C1 = "=RIGHT(RC[-1]:R[17]C[-1],5)"

Next cell
Next addr
End Sub

thanks
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Different Results from the Same Macro 3
Copying Data Down the Column But Only Based on What's in Column A 3
Creating Pivot Table with Macro using Multiple Sheets 0
Running Excel Code from Access 9
vlookup working in one instance, but not another 3
changing range in recorded macro 1
Running an Excel Macro from Access 6
Code Efficiency Suggestions 33

Top