Macro that pulls variables from Text file?

[gilbert]

I am trying to find a macro that will pull addresses from a text file. The text file is all of the rental code violation writeups in my city. I need to pull the address out of the file and manipulate it just to get the street number and street name in their own column. I do not know excel well enough to find all the unwanted text out and leave the rest.

An example is:

David Williams ChaC2 14.00 3315-3221 N DAVIDSON ST H 11 Complaint 08/21/03 08/21/03 08/25/03 H20030821005

All I need is 3315 (in one column) and DAVIDSON (in another)

And all I need is this address portion. None of the other data is needed. And from the address in this example I do not need the unit (-3221) nor the street direction (N) nor the street type (ST).

Is there a macro that could be customized to fit this? I am including a large portion of the text file which is in Word rich text format. I would greatly appreciate any help.

Galen Massey
galen@realpointonline.com

File follows:


Inspections Initiated
for period: 8/1/2003 - 10/7/2003
Group By: CensusTract Sort By: Address Ascending
Inspector N'hood/ Census Site Insp Origin Date Date Date Date Docket
Name Address Type Entered Assigned Inspected Complete Number
FocArea Tract Type
Charles T Hodges 104102 WILLOW BEND CR H 11 Complaint 08/28/03 08/29/03 09/11/03 09/11/03 H20030828008
Paris G Waddy 12216 LULLINGSTONE RD H 17 Relocation 08/05/03 08/05/03 08/05/03 08/05/03 H20030805003
Zafar Shums 18011 NEW MARK AV H 12 Field Observance 08/22/03 08/22/03 08/22/03 H20030822001
Paris G Waddy 3406 MARVIN RD H 12 Field Observance 08/07/03 08/07/03 09/10/03 H20030807002
Paris G Waddy 3733 ELLINGTON ST H 12 Field Observance 08/07/03 08/07/03 08/07/03 H20030807001
Paris G Waddy 3733 ELLINGTON ST H 12 Field Observance 08/13/03 08/13/03 08/15/03 H20030813001
Ken Eaker 510-101 OAKWOOD CREEK H 11 Complaint 08/25/03 08/27/03 09/25/03 H20030825014
Paris G Waddy 5801-B OLD PROVIDENCE H 11 Complaint 08/29/03 08/29/03 09/18/03 09/18/03 H20030829007
Paris G Waddy 6917 STILLMEADOW DRIVE H 17 Relocation 08/29/03 08/29/03 09/03/03 09/09/03 H20030829011
Totals for
Total initiated for selected period 9
David Williams ChaC2 10.00 1434 HAMORTON PL H 11 Complaint 08/18/03 08/18/03 08/29/03 08/29/03 H20030818025
Totals for 10.00
Total initiated for selected period 1
Paris G Waddy Baker1 11.00 610-H BRANLET ROAD H 11 Complaint 08/29/03 08/29/03 09/09/03 H20030829005
Totals for 11.00
Total initiated for selected period 1
David Williams ChaC3 12.00 3015 AIRLIE STREET H 17 Relocation 08/20/03 08/20/03 08/25/03 09/19/03 H20030820001
Totals for 12.00
Total initiated for selected period 1
Zafar Shums ChaC2 13.00 1325 MATHESON AV H 11 Complaint 08/04/03 08/05/03 08/11/03 H20030804005
David Williams ChaC2 13.00 1400 ACADEMY STREET H 11 Complaint 07/30/03 07/31/03 08/25/03 H20030730004
Zafar Shums ChaC2 13.00 1618-88 HERRIN AV H 11 Complaint 08/04/03 08/05/03 08/08/03 08/08/03 H20030804010
David Williams ChaC2 13.00 1808 ACADEMY ST H 15 Fire 08/15/03 08/15/03 08/18/03 H20030815012
David Williams ChaC2 13.00 3106 FLORIDA AVENUE H 11 Complaint 07/24/03 07/29/03 08/05/03 08/08/03 H20030724010
David Williams ChaC2 13.00 3227 ERSKINE DRIVE H 17 Relocation 08/08/03 08/08/03 08/20/03 08/20/03 H20030808002
Totals for 13.00
Total initiated for selected period 6
Tuesday, October 07, 2003 Page 1 of 19

Inspections Initiated
for period: 8/1/2003 - 10/7/2003
Group By: CensusTract Sort By: Address Ascending
Inspector N'hood/ Census Site Insp Origin Date Date Date Date Docket
Name Address Type Entered Assigned Inspected Complete Number
FocArea Tract Type
Jimmy Womack ChaC2 14.00 2809 CLEMSON AV H 11 Complaint 08/04/03 08/05/03 08/08/03 H20030804009
Eric S McManus ChaC2 14.00 3000 CLEMSON AVENUE H 19 Public Agency 09/19/03 09/19/03 10/02/03 H20030919007
David Williams ChaC2 14.00 3315-3221 N DAVIDSON ST H 11 Complaint 08/21/03 08/21/03 08/25/03 H20030821005
David Williams ChaC2 14.00 3315-3316 N DAVIDSON H 11 Complaint 09/03/03 09/04/03 09/08/03 H20030903001
David Williams ChaC2 14.00 3931 BEARWOOD AV H 11 Complaint 08/01/03 08/02/03 08/29/03 08/29/03 H20030801003
David Williams ChaC2 14.00 4034 REDWOOD AVENUE H 17 Relocation 09/04/03 09/04/03 09/08/03 09/23/03 H20030904007
Totals for 14.00
Total initiated for selected period 6
David Williams ChaC3 15.01 1112 FARRIOR DRIVE H 11 Complaint 09/02/03 09/02/03 09/09/03 09/09/03 H20030902010
David Williams ChaC3 15.01 4717 HIGHLAKE DRIVE H 11 Complaint 07/28/03 07/29/03 08/25/03 H20030728006
Charles L Hitsman ChaC3 15.01 634 CAMROSE DR H 11 Complaint 07/24/03 07/29/03 08/18/03 H20030724027
David Williams ChaC3 15.01 6418-F THE PLAZA H 11 Complaint 08/26/03 08/27/03 08/28/03 H20030826005
Totals for 15.01
Total initiated for selected period 4
Charles L Hitsman ChaC3 15.03 6920 CEDARCREEK DRIVE H 11 Complaint 09/23/03 09/23/03 09/30/03 09/30/03 H20030923003
Totals for 15.03
Total initiated for selected period 1
Paris G Waddy ChaC3 15.04 3512 DONOVAN PL H 11 Complaint 08/25/03 08/27/03 09/03/03 H20030825012
Charles L Hitsman 15.04 3932 COLEBROOK ROAD H 17 Relocation 08/21/03 08/21/03 08/22/03 09/05/03 H20030821002
Ken Eaker Adam3 15.04 7136-J VILLAGE GREEN DR H 11 Complaint 08/04/03 08/05/03 08/07/03 H20030804015
Totals for 15.04
Total initiated for selected period 3
Charles L Hitsman ChaC2 16.02 3015-210 TIMBER HOLLOW H 11 Complaint 09/03/03 09/04/03 09/09/03 09/09/03 H20030903002
Charles L Hitsman ChaC2 16.02 3513 HAVENWOOD ROAD H 18 Petition 09/17/03 09/17/03 09/18/03 H20030917004
Spencer Stickell ChaC2 16.02 3743 SUDBURY RD H 15 Fire 03/13/03 03/14/03 08/04/03 H20030313006
Charles L Hitsman ChaC2 16.02 4210 WOODLEAF RD H 18 Petition 08/06/03 08/07/03 08/27/03 H20030806006
Charles L Hitsman ChaC2 16.02 4305-H CENTRAL AV H 11 Complaint 08/18/03 08/18/03 08/21/03 08/21/03 H20030818001
Ken Eaker ChaC2 16.02 4945-7 CENTRAL AVENUE H 11 Complaint 08/26/03 08/27/03 09/09/03 10/01/03 H20030826009
Charles L Hitsman ChaC2 16.02 5001 CARRIAGE DRIVE H 19 Public Agency 09/17/03 09/17/03 09/17/03 H20030917005
Tuesday, October 07, 2003 Page 2 of 19

Inspections Initiated
for period: 8/1/2003 - 10/7/2003
Group By: CensusTract Sort By: Address Ascending
Inspector N'hood/ Census Site Insp Origin Date Date Date Date Docket
Name Address Type Entered Assigned Inspected Complete Number
FocArea Tract Type
Totals for 16.02
Total initiated for selected period 7
Charles L Hitsman ChaC3 16.04 3265-H SHAMROCK DRIVE H 11 Complaint 09/18/03 09/18/03 10/06/03 H20030918002
Charles L Hitsman ChaC3 16.04 3616 FRONTENAC AVENUE H 11 Complaint 09/11/03 09/12/03 09/15/03 H20030911007
Charles L Hitsman ChaC3 16.04 5004-P ENDOLWOOD ROAD H 11 Complaint 07/25/03 07/29/03 08/18/03 09/18/03 H20030725014
Totals for 16.04
Total initiated for selected period 3
Charles L Hitsman ChaC1 17.02 937-A NORLAND RD H 18 Petition 08/15/03 08/15/03 09/03/03 H20030815001
Charles L Hitsman ChaC1 17.02 937-B NORLAND RD H 18 Petition 08/15/03 08/15/03 09/03/03 H20030815011
Totals for 17.02
Total initiated for selected period 2
Charles L Hitsman ChaC2 17.98 1407-102 EASTCREST DRIVE H 11 Complaint 07/25/03 07/29/03 08/26/03 08/26/03 H20030725007
Paris G Waddy ChaC2 17.98 1523-B BRIAR CREEK RD H 11 Complaint 08/27/03 08/28/03 09/03/03 H20030827006
Charles L Hitsman ChaC2 17.98 1717-D EASTCREST DRIVE H 17 Relocation 08/11/03 08/11/03 08/15/03 08/21/03 H20030811003
Charles L Hitsman ChaC2 17.98 3036-101 PEACH BOTTOM H 11 Complaint 07/25/03 07/29/03 08/18/03 09/18/03 H20030725010
Charles L Hitsman ChaC2 17.98 3901 LANGHORNE AV H 18 Petition 08/06/03 08/07/03 08/21/03 H20030806005
Charles L Hitsman ChaC2 17.98 5421-V ALBEMARLE ROAD H 11 Complaint 09/11/03 09/12/03 09/23/03 09/30/03 H20030911010
David Williams ChaC2 17.98 5425-R ALBEMARLE RD H 11 Complaint 08/26/03 08/27/03 09/04/03 H20030826004
Totals for 17.98
Total initiated for selected period 7
Jimmy Womack Baker1 18.00 524-A BRAMLET ROAD H 12 Field Observance 09/29/03 09/29/03 09/30/03 H20030929011
Jimmy Womack Baker1 18.00 524-B BRAMLET ROAD H 12 Field Observance 09/29/03 09/29/03 09/30/03 H20030929012
Jimmy Womack Baker1 18.00 524-C BRAMLET ROAD H 12 Field Observance 09/29/03 09/29/03 10/01/03 H20030929013
Jimmy Womack Baker1 18.00 531-A COLONNADE DRIVE H 12 Field Observance 09/29/03 09/29/03 09/30/03 H20030929006
Jimmy Womack Baker1 18.00 531-B COLONNADE DRIVE H 12 Field Observance 09/29/03 09/29/03 09/30/03 H20030929008
Jimmy Womack Baker1 18.00 531-C COLONNADE DRIVE H 12 Field Observance 09/29/03 09/29/03 09/30/03 H20030929010
Totals for 18.00
Total initiated for selected period 6
Tuesday, October 07, 2003 Page 3 of 19

Inspections Initiated
for period: 8/1/2003 - 10/7/2003
Group By: CensusTract Sort By: Address Ascending
Inspector N'hood/ Census Site Insp Origin Date Date Date Date Docket
Name Address Type Entered Assigned Inspected Complete Number
FocArea Tract Type
Jimmy Womack Baker1 18.98 2113-B EATON ROAD H 11 Complaint 09/19/03 09/19/03 09/24/03 H20030919010
Jimmy Womack Baker1 18.98 3801 MONROE ROAD H 12 Field Observance 08/25/03 08/25/03 09/24/03 09/24/03 H20030825006
Jimmy Womack Baker1 18.98 4427-A MONROE ROAD H 12 Field Observance 08/27/03 08/27/03 09/03/03 H20030827010
Jimmy Womack Baker1 18.98 4427-B MONROE ROAD H 12 Field Observance 08/27/03 08/27/03 09/03/03 H20030827011
Jimmy Womack Baker1 18.98 5301 KELLY (AKA 1900 H 11 Complaint 08/27/03 08/27/03 09/03/03 H20030827009
Jimmy Womack Baker1 18.98 640-5 CHIPLEY AVENUE H 11 Complaint 09/09/03 09/09/03 09/10/03 H20030909009
Jimmy Womack Baker1 18.98 640-6 CHIPLEY AV H 11 Complaint 08/06/03 08/07/03 08/08/03 09/10/03 H20030806003
Totals for 18.98
Total initiated for selected period 7
Libbie M Webber Baker3 19.03 5739 ELECTRA LANE H 11 Complaint 09/22/03 09/22/03 09/29/03 H20030922012
Libbie M Webber Baker3 19.03 7115 MONROE ROAD H 11 Complaint 09/05/03 09/05/03 09/19/03 09/19/03 H20030905007
Libbie M Webber Baker3 19.03 8145 CEDAR GLEN DR H 11 Complaint 08/04/03 08/05/03 09/19/03 09/19/03 H20030804003
Jimmy Womack Baker3 19.03 8145 CEDAR GLEN DR H 11 Complaint 08/04/03 08/04/03 08/05/03 H20030804017
Jimmy Womack Baker3 19.03 912 PINAFORE DR H 11 Complaint 08/18/03 09/02/03 09/04/03 09/04/03 H20030818029
Jimmy Womack Baker3 19.03 912 PINAFORE DR H 11 Complaint 08/18/03 08/19/03 08/26/03 H20030818031
Totals for 19.03
Total initiated for selected period 6
Charles L Hitsman Baker3 19.07 6320 OLD MEADOW ROAD H 11 Complaint 10/02/03 10/02/03 10/06/03 H20031002002
Paris G Waddy Baker3 19.07 9720 STILLWATER LN H 11 Complaint 07/24/03 07/28/03 08/05/03 08/19/03 H20030724022
Totals for 19.07
Total initiated for selected period 2
Libbie M Webber Baker3 19.08 5739-2 CEDARS EAST CT H 11 Complaint 08/12/03 08/13/03 09/19/03 09/19/03 H20030812012
Libbie M Webber Baker3 19.08 5743-3 CEDARS EAST CT H 11 Complaint 08/04/03 08/05/03 09/05/03 H20030804007
Jimmy Womack Baker3 19.08 5743-4 CEDARS EAST COURT H 11 Complaint 08/21/03 08/22/03 08/22/03 09/11/03 H20030821003
John A Davis Baker3 19.08 5828-B12 REDDMAN RD H 11 Complaint 08/12/03 08/13/03 09/25/03 H20030812016
Libbie M Webber 19.08 5831-15 REDDMAN RD H 18 Petition 09/17/03 09/18/03 09/22/03 H20030917001
Totals for 19.08
Total initiated for selected period 5
Libbie M Webber Baker3 19.09 5711-A ELECTRA LANE H 11 Complaint 09/16/03 09/16/03 09/29/03 H20030916001
Tuesday, October 07, 2003 Page 4 of 19

Inspections Initiated
for period: 8/1/2003 - 10/7/2003
Group By: CensusTract Sort By: Address Ascending
Inspector N'hood/ Census Site Insp Origin Date Date Date Date Docket
Name Address Type Entered Assigned Inspected Complete Number
FocArea Tract Type
Totals for 19.09
Total initiated for selected period 1
Paris G Waddy Baker3 19.10 4425 DAWNWOOD DRIVE H 11 Complaint 08/27/03 08/27/03 09/10/03 H20030827012
Libbie M Webber Baker3 19.10 5901 FARM POND LANE H 11 Complaint 09/05/03 09/05/03 09/19/03 09/19/03 H20030905008
Charles L Hitsman Baker3 19.10 6325-I WOODBEND DR H 17 Relocation 07/24/03 07/24/03 08/22/03 08/22/03 H20030724024
Paris G Waddy Baker3 19.10 6325-L WOODBEND DRIVE H 17 Relocation 08/21/03 08/22/03 08/26/03 09/03/03 H20030821004
Paris G Waddy Baker3 19.10 6409 YATESWOOD DR H 11 Complaint 06/25/03 06/30/03 08/18/03 08/18/03 H20030625016
Paris G Waddy Baker3 19.10 6602-A IDLEWILD RD H 11 Complaint 08/12/03 08/13/03 08/15/03 08/20/03 H20030812015
Libbie M Webber Baker3 19.10 7017-C SNOW LN H 11 Complaint 08/06/03 08/07/03 09/18/03 H20030806009
Charles E Tinsley Baker3 19.10 7113-2 SNOW LN H 11 Complaint 06/16/03 06/24/03 08/18/03 08/18/03 H20030616001
Libbie M Webber Baker3 19.10 7132-F SNOW LN H 11 Complaint 08/12/03 08/13/03 09/19/03 09/19/03 H20030812006
Totals for 19.10
Total initiated for selected period 9
Paris G Waddy Baker3 19.11 641 ALLISTER DRIVE H 11 Complaint 08/27/03 08/27/03 09/09/03 H20030827016
Libbie M Webber Baker3 19.11 8501 MISSION HILLS ROAD H 11 Complaint 09/05/03 09/05/03 09/19/03 09/23/03 H20030905005
Totals for 19.11
Total initiated for selected period 2
Spencer Stickell 19.12 5401-D SPRINGSET DRIVE H 11 Complaint 07/25/03 07/25/03 08/05/03 09/05/03 H20030725003
Charles L Hitsman ChaC3 19.12 5451-G SPRINGSET DR H 11 Complaint 08/01/03 08/02/03 08/18/03 H20030801002
Charles L Hitsman ChaC3 19.12 5842 HICKORY GROVE H 12 Field Observance 08/13/03 08/13/03 08/18/03 09/23/03 H20030813002
Jimmy Womack ChaC3 19.12 6409-97 FOUR SEASONS LN H 11 Complaint 08/04/03 08/05/03 08/08/03 08/27/03 H20030804014
Charles L Hitsman ChaC3 19.12 6503-A FOUR WINDS DRIVE H 11 Complaint 07/28/03 07/29/03 08/18/03 H20030728004
Totals for 19.12
Total initiated for selected period 5
Charles L Hitsman ChaC3 19.13 8234 IVY HOLLOW DR H 11 Complaint 07/24/03 07/29/03 08/18/03 H20030724025
Totals for 19.13
Total initiated for selected period 1
Jimmy Womack Baker1 21.00 1010 NANCY DRIVE H 11 Complaint 09/19/03 09/19/03 09/24/03 H20030919009
Tuesday, October 07, 2003 Page 5 of 19

Inspections Initiated
for period: 8/1/2003 - 10/7/2003
Group By: CensusTract Sort By: Address Ascending
Inspector N'hood/ Census Site Insp Origin Date Date Date Date Docket
Name Address Type Entered Assigned Inspected Complete Number
FocArea Tract Type
Jimmy Womack Baker1 21.00 4212 WALKER ROAD H 12 Field Observance 08/25/03 08/25/03 08/27/03 H20030825004
Totals for 21.00
Total initiated for selected period 2
Charles E Tinsley Baker1 23.00 1045-A N WENDOVER RD H 17 Relocation 06/11/03 06/19/03 08/18/03 08/18/03 H20030611001
Charles E Tinsley Baker1 23.00 1048-A N WENDOVER RD H 17 Relocation 06/23/03 06/24/03 08/18/03 08/18/03 H20030623014
Paris G Waddy 059 23.00 1054-D N WENDOVER RD H 17 Relocation 10/01/03 10/01/03 10/01/03 H20031001004
Jimmy Womack Baker1 23.00 3408-E OAK ARBOR LANE H 11 Complaint 07/30/03 07/30/03 08/03/03 09/17/03 H20030730007
Jimmy Womack Baker1 23.00 3408-F OAK ARBOR LANE H 11 Complaint 08/08/03 08/08/03 08/08/03 09/05/03 H20030808003
Jimmy Womack Baker1 23.00 3411-E OAK ARBOR LN H 11 Complaint 08/06/03 08/07/03 08/26/03 09/12/03 H20030806010
Jimmy Womack Baker1 23.00 3439-C OAK ARBOR LANE H 11 Complaint 08/27/03 08/27/03 09/03/03 H20030827015
Charles E Tinsley Baker1 23.00 422 SKYLAND AVE H 19 Public Agency 05/29/03 06/03/03 08/21/03 08/21/03 H20030529014
Jimmy Womack Baker1 23.00 441 SKYLAND AVENUE H 12 Field Observance 09/29/03 09/29/03 09/29/03 H20030929002
Charles E Tinsley Baker1 23.00 624-G BILLINGSLEY RD H 19 Public Agency 06/04/03 06/19/03 09/08/03 09/08/03 H20030604004
Jimmy Womack Baker1 23.00 815-1 VILLA COURT H 12 Field Observance 09/12/03 09/12/03 09/12/03 H20030912005
Jimmy Womack Baker1 23.00 815-2 VILLA COURT H 12 Field Observance 09/12/03 09/12/03 09/12/03 H20030912006
Jimmy Womack Baker1 23.00 815-3 VILLA COURT H 12 Field Observance 09/12/03 09/12/03 09/12/03 H20030912007
Jimmy Womack Baker1 23.00 815-5 VILLA COURT H 12 Field Observance 09/12/03 09/12/03 09/12/03 H20030912008
Jimmy Womack Baker1 23.00 815-7 VILLA COURT H 12 Field Observance 09/12/03 09/12/03 09/12/03 H20030912009
Jimmy Womack Baker1 23.00 817-1 VILLA COURT H 12 Field Observance 09/12/03 09/12/03 09/12/03 H20030912010
Jimmy Womack Baker1 23.00 817-4 VILLA COURT H 12 Field Observance 09/12/03 09/12/03 09/12/03 H20030912011
Jimmy Womack Baker1 23.00 817-5 VILLA COURT H 12 Field Observance 09/12/03 09/12/03 09/12/03 H20030912012
Ron Simmons Baker1 23.00 819-5 VILLA COURT H 12 Field Observance 09/12/03 09/12/03 09/18/03 H20030912003
Ron Simmons Baker1 23.00 819-6 VILLA COURT H 12 Field Observance 09/12/03 09/12/03 09/18/03 H20030912004
Totals for 23.00
Total initiated for selected period 20
Brenda Allison Baker1 24.00 2540 E 7TH ST H 11 Complaint 08/12/03 08/13/03 09/18/03 H20030812014
Totals for 24.00
Total initiated for selected period 1
Ken Eaker Baker1 26.00 409 ELI STREET H 12 Field Observance 09/05/03 09/05/03 09/11/03 H20030905001
Tuesday, October 07, 2003 Page 6 of 19

Inspections Initiated
for period: 8/1/2003 - 10/7/2003
Group By: CensusTract Sort By: Address Ascending
Inspector N'hood/ Census Site Insp Origin Date Date Date Date Docket
Name Address Type Entered Assigned Inspected Complete Number
FocArea Tract Type
Totals for 26.00
Total initiated for selected period 1
Ron Simmons Baker1 29.01 424 SCOFIELD ROAD H 12 Field Observance 09/09/03 09/09/03 09/12/03 H20030909010
Totals for 29.01
Total initiated for selected period 1
Jimmy Womack Baker2 30.05 610-H BRAMLET ROAD H 11 Complaint 08/29/03 09/02/03 09/10/03 10/02/03 H20030829009
Totals for 30.05
Total initiated for selected period 1
Zafar Shums Baker2 30.06 3015 WAMATH DRIVE H 19 Public Agency 09/09/03 09/09/03 09/15/03 09/15/03 H20030909002
Jimmy Womack Baker2 30.06 5009 SHARON ROAD H 11 Complaint 08/29/03 09/02/03 09/24/03 09/24/03 H20030829008
John A Davis Baker2 30.06 5009-E SHARON ROAD H 11 Complaint 08/29/03 08/29/03 08/29/03 08/29/03 H20030829006
Totals for 30.06
Total initiated for selected period 3
Charles T Hodges Adam2 31.03 1331-13 ABBEY PL H 11 Complaint 09/19/03 09/19/03 10/01/03 H20030919013
Jake Osborne Adam2 31.03 1342 ABBEY PLACE PLACE H 11 Complaint 09/19/03 09/19/03 09/23/03 H20030919011
Jake Osborne Adam2 31.03 4943-305 PARK ROAD H 11 Complaint 09/09/03 09/09/03 09/22/03 H20030909013
Jake Osborne Adam2 31.03 4943-406 PARK ROAD H 11 Complaint 09/12/03 09/12/03 09/25/03 H20030912002
Totals for 31.03

 

[Andrew Poulsom]

That's going to be pretty nigh impossible, even with a Macro, because there is no logical pattern. What happens with:

NEW MARK AV ....... MARK?
OLD PROVIDENCE H ....... PROVIDENCE?
THE PLAZA H ....... PLAZA?
DONOVAN PL H ....... PL?
VILLAGE GREEN DR ....... GREEN?
TIMBER HOLLOW H ....... HOLLOW?

I suggest that you get whoever produced the text file to produce another with different criteria (I see "Group By: CensusTract Sort By: Address Ascending") and fewer fields.

 

[Damon Ostrander]

Hi Gilbert,

Writing code that extracts the two item based on rules you define is easy. But it looks to me like defining the rules is very hard. For example, what would be the rule for finding the number (3315 in your example)? There does not seem to be anything straightforward to key off of! In your example the rule:

"pick the first numeric field after a decimal numeric field and before a hyphen"

would work for the example, but not for many other records in your file, such as the Waddy records that don't contain any decimal value.

When I look at your file, I don't see any simple rule for consistently identifying the address field.

Similiarly with the street, in which there is no way to know what words to include in the street name without an exhaustive table of street prefixes and suffixes. For example Old Davidson, Old Davidson Pl, Davidson Place, Davidson NE, etc., where the code would need to recognize Pl, Place, Way, Av, Ave, Avenue, etc., if you want the code to be able to strip off these suffixes where they occur.

Sorry to make this so complicated, but parsing addresses with all their myriad variations is not trivial.

Damon