Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
remove street address from address
- Thread starter althea
- Start date
SkipVought
Programmer
Yes, this will take a winnowing of the wheat from the chaff.
Much analysis.
Many passes.
LOTS fo hard work!
There is NOT ONE solution.
There will be many progressive solutions, maybe, ending up with yet alot of manual pencil-whipping.
It's a MESS!
Skip,
[sub]
![[glasses]](/data/assets/smilies/glasses.gif "[glasses] [glasses]")
To be safe on the [red]FOURTH[/red],Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]
![[tongue]](/data/assets/smilies/tongue.gif "[tongue] [tongue]")
[/sub]Hi Althea
The main problem as already mentioned is the varying number of words making up the Street and the town-state-zipcode.
My approach for a similar problem was this.
1 On a COPY of the worksheet
Insert new column A,do a fill series 1...40000 make header "Index" . This will allow you to sort on Column A in the last step.
2 Assumming address in column B perform Data/Text to columns using the "Space" as a delimiator. This will give you separate column for each word/number in the address.
3 Create heading for each col ie add1..add2..add3 etc
4 Then sort on the column to the far right. This approach will allow you to realign all data referring to "Street" in multiple rows of similar addresses length. Insert extra column where needed to align the columns. By this I mean rows with 3 column for address, 2 for town and visa versa.
Then sort on the 2nd,3rd...column from the right and repeat.
5.Your then in a position to concatenate in a new column the various parts of the address with the following formula and a second column for the town/city/zipcode. eg.
=TRIM(B1&" "&C1&" "&D1&" "&E1)
the trim will eliminate any extra spaces for blank column.
6 Copy and paste-special/values.Then delete unnecessary columns
7 Finally Re-sort on column A created in step 1.
This may sound tedious but I think the number of different groupings would be 5 or 6 and your letting excel do the bulk of the work.
This worked for me in live situation. Just an alternative approach.
Regards
Laurie
The main problem as already mentioned is the varying number of words making up the Street and the town-state-zipcode.
My approach for a similar problem was this.
1 On a COPY of the worksheet
Insert new column A,do a fill series 1...40000 make header "Index" . This will allow you to sort on Column A in the last step.
2 Assumming address in column B perform Data/Text to columns using the "Space" as a delimiator. This will give you separate column for each word/number in the address.
3 Create heading for each col ie add1..add2..add3 etc
4 Then sort on the column to the far right. This approach will allow you to realign all data referring to "Street" in multiple rows of similar addresses length. Insert extra column where needed to align the columns. By this I mean rows with 3 column for address, 2 for town and visa versa.
Then sort on the 2nd,3rd...column from the right and repeat.
5.Your then in a position to concatenate in a new column the various parts of the address with the following formula and a second column for the town/city/zipcode. eg.
=TRIM(B1&" "&C1&" "&D1&" "&E1)
the trim will eliminate any extra spaces for blank column.
6 Copy and paste-special/values.Then delete unnecessary columns
7 Finally Re-sort on column A created in step 1.
This may sound tedious but I think the number of different groupings would be 5 or 6 and your letting excel do the bulk of the work.
This worked for me in live situation. Just an alternative approach.
Regards
Laurie
- Status
Similar threads
- Question