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." wrote in message
...
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" wrote:

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" wrote in message
...
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