asked on

Convert Address to Block Numbers

Hello,

I am trying to take an address string and convert the specific address to a block number.

For example: 123 N Main Street
would become: 100 Block N Main Street

Since the address string may or may not start with a number (the address could be Main / First) and since that number could be anywhere between 1 and 6 characters long, I'm a bit stumped.

I can use an if/then/else and a numeric(left(fieldname,X)) statement to determine if it starts with a number but then I need to convert that number to blocks, strip the text it is replacing, add the word Block and then the rest of the field.

I swear I saw (and maybe even used) such a formula but I can't find it anywhere. Any help is appreciated.

Mike McCracken

You can use the VAL function to get the value of the address number

Val ("123 N Main Street ") = 123
Val(" N Main Street") = 0

What do you want 10 Main street to return?

Try this idea

If Val({StreetAddressFIeld}) > 0 then
CStr(Val({StreetAddressFIeld}) \ 100 * 100,0,"") & Mid({StreetAddressFIeld}, InStr({StreetAddressField}," ")
Else
{StreetAddressFIeld}

Another way would be

Local StringVar Array AddressParts;
AddressParts := Split({StreetAddressFIeld}, " ")
If Val(AddressParts[1] > 0 then
AddressParts[1] := CStr(Val(AddressParts[1]) \ 100 * 100,0,"");
Join(AddressParts," ")

mlmcc

Dragonnns

ASKER

The first formula converts 7002 Spaniel Road to 07002 Spaniel Road, 11802 Peoria Street to 111802 Peoria Street and 1105 Ohio Street to 01105 Ohio Street.

The second one I couldn't get to work.

For those under 100, I want to return 0 Block.

Dragonnns

ASKER

This did get me on a path to make this work. This is what I ended up doing. There may be a simpler way but this works for now.

NumberVar Lengths := If Val({StreetAddressField}) > 0 then Len(ToText(Val({StreetAddressField}))) + 1 ;

StringVar Address := Right({StreetAddressField}, len({StreetAddressField})-Lengths +1) ;

If {StreetAddressField} StartsWith ToText(Val({StreetAddressField}),0,'') + 'ST' then {StreetAddressField} else
If {StreetAddressField} StartsWith ToText(Val({StreetAddressField}),0,'') + 'ND' then {StreetAddressField} else
If {StreetAddressField} StartsWith ToText(Val({StreetAddressField}),0,'') + 'RD' then {StreetAddressField} else
If {StreetAddressField} StartsWith ToText(Val({StreetAddressField}),0,'') + 'TH' then {StreetAddressField} else
If Val({StreetAddressField}) > 0 then
CStr((Truncate(Val({StreetAddressField})/100)*100),0,"") + ' BLOCK ' + Address
else
{StreetAddressField} ;

Mike McCracken

Try this one

If Val({StreetAddressFIeld}) > 0 then
CStr((Val({StreetAddressFIeld}) \ 100) * 100,0,"") & Mid({StreetAddressFIeld}, InStr({StreetAddressField}," "))
Else
{StreetAddressFIeld}

It seems Crystal is doing the division incorrectly. It seems the 100 * 100 was done first then the division.

The other one should be
Local StringVar Array AddressParts;

AddressParts := Split({StreetAddressFIeld}, " ");
If Val(AddressParts[1]) > 0 then
AddressParts[1] := CStr((Val(AddressParts[1]) \ 100) * 100,0,"");
Join(AddressParts," ")

mlmcc

Dragonnns

ASKER

The LocalArray works better than your first formula but neither address addresses such as 5th and Main (which exist in this database).

Though not elegant, the formula I posted works. I wouldn't have gotten there without your help though!

Thanks!

Mike McCracken

I had assumed the address would always start with a number then street or just the street. Didn't think about 5th and Main

Try this

Local StringVar Array AddressParts;

AddressParts := Split({StreetAddressFIeld}, " ");
If
If IsNumeric(AddressParts[1]) > 0 then
AddressParts[1] := CStr((Val(AddressParts[1]) \ 100) * 100,0,"");
Join(AddressParts," ")

mlmcc

Dragonnns

ASKER

I get a "The keyword 'then' is missing" error message with your formula. If I remove the duplicate "If", I get a "A number, currency amount, date, time, date-time, or string is required here." and places my cursor on the "IsNumeric(AddressParts[1])" field.

Thoughts?

ASKER CERTIFIED SOLUTION

Mike McCracken

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dragonnns

ASKER

Now that works. :)

Thanks!