how do i separate addresses and place results in another cell

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

maju - 19 May 2008 15:37 GMT

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

Reply to this Message

merjet - 19 May 2008 16:09 GMT

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

Reply to this Message

Bob Phillips - 19 May 2008 16:39 GMT

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

Signature

---
HTH

Bob

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

>I am using excel 2003.
> I will like to know how i can use the mid/left/right function to separate
[quoted text clipped - 33 lines]
>
> thanks

Reply to this Message

Mike H. - 19 May 2008 20:50 GMT

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?

> Public Sub finalseparate_address()
> With Columns("B:D")
[quoted text clipped - 70 lines]
> >
> > thanks

Reply to this Message

Bob Phillips - 19 May 2008 23:08 GMT

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

Signature

---
HTH

Bob

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

> Bob, when I do a simple Copy and paste of your code, I can't get it to
> work.
[quoted text clipped - 84 lines]
>> >
>> > thanks

Reply to this Message