x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 1797

# 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.
0
Dragonnns
• 5
• 4
1 Solution

Commented:
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

Else

Another way would be

mlmcc
0

Author Commented:
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.
0

Author Commented:
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 ;

else
0

Commented:
Try this one

Else

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

The other one should be

mlmcc
0

Author Commented:
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!
0

Commented:

Try this

If

mlmcc
0

Author Commented:
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?
0

Commented:
Sorry.  That's what I I get for editing and not trying it.

``````Local StringVar Array AddressParts;

``````

mlmcc
0

Author Commented:
Now that works. :)

Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.