Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > how do i separate addresses and place results in another cell
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 19
Default how do i separate addresses and place results in another cell
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 With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 812
Default how do i separate addresses and place results in another cell
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 With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10,593
Default how do i separate addresses and place results in another cell
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




Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 471
Default how do i separate addresses and place results in another cell
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





Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10,593
Default how do i separate addresses and place results in another cell
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








Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to separate names and addresses in a spreadsheet Basil Excel Worksheet Functions 1 June 4th 08 05:11 AM
merge a wordfile of names & addresses to an excel separate cells John Excel Worksheet Functions 1 April 6th 07 09:22 PM
answer to odd results when comparing hyperlink addresses Patricia Shannon Links and Linking in Excel 0 March 9th 06 07:28 PM
can vlookup place results in a different box than the formula? Psychlogic Excel Worksheet Functions 6 January 24th 06 11:10 PM
How 2 separate column of addresses into a colum of names and numb Rochelle Excel Discussion (Misc queries) 3 April 26th 05 10:02 AM


All times are GMT +1. The time now is 08:32 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement