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