Deleting numbers from an address field

[Abner2]

I have a report that lists crimes occuring on different addresses within our city. I would like to take out the street number from the address field and just show the street names only. What would the formula look like?

Thanks

 

[synapsevampire]

Try:

mid({Customer.Address},instr(" ",{Customer.Address})+1)

This will show everything past the first space in the address, but it's far from foolproof.

There are many software packages which parse addresses, including using CD's available from the Post Office.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[SmpTraining]

// Formula to remove the leading numeric values (and spaces) from a string
//
// To use, replace the text assigned to OldString with your address field
//

StringVar OldString:= " 298 Redwood Grove";
StringVar NewString:="";

NumberVar i:=1;

For i := 1 to len(OldString)
do
(
If not (IsNumeric (OldString) or
OldString = " &quot then
(
NewString := Mid (OldString, i, len(OldString) - i +1);
Exit for
);
);

Newstring; Steve Phillips, Crystal Consultant

 

[SmpTraining]

Sorry, using i as a counter caused some formatting problems, here's the formula again


// Formula to remove the leading numeric values (and spaces) from a string
//
// To use, replace the text assigned to OldString with your address field
//

StringVar OldString:= " 298 Redwood Grove";
StringVar NewString:="";

NumberVar j:=1;

For j := 1 to len(OldString)
do
(
If not (IsNumeric (OldString[j]) or
OldString[j] = " &quot then
(
NewString := Mid (OldString, j, (len(OldString) - j) +1 );
Exit for
);
);

Newstring;
Steve Phillips, Crystal Consultant

 

[synapsevampire]

Ahhh, good point, Steve, I should have checked for isnumeric:

if isnumeric(left({Customer.Address1},instr({Customer.Address1}," &quot)) then
mid({Customer.Address1},instr({Customer.Address1}," &quot+1)
else
{Customer.Address1}

I tend to avoid using variables because they destroy pass through SQL, so I just don't use them anywhere anymore unless required (looping, etc.).

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[synapsevampire]

Ahhh, good point, Steve, I could have at least checked isnumeric:

if isnumeric(left({Customer.Address1},instr({Customer.Address1}," &quot)) then
mid({Customer.Address1},instr({Customer.Address1}," &quot+1)
else
{Customer.Address1}

I tend to avoid using variables because they destroy pass through SQL, so I just don't use them anywhere anymore unless required (looping, etc.).

BTW, that's far from foolproof, addresses such as 49A Main Street would not get stripped.

The best solution is to use software to parse the address into each component, the next would be to write some lengthy code to perform numerous checks, including your isnumeric, failing that, check for anything within the first part that is numeric, and that there's logical information after that part (contains St., Rd., Street, Road, etc.). Also there's usually more than one address line in a database, so getting software that reads the addresses and then writes (to different fields so as not to destroy the integrity) the USPS standardized address information, as well as being able to parse into different components is very ijmportant, especially when trying to dedupe a large mailing list (mailings can be VERY expensive).

I wrote a very comprehensive Fox program in the late 80's to accomplish this as the market software was weak and very expensive. To get very accurate information I had lookup tables and ever increasing exceptions.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com