seperate address into 2 fields and remove leading zeros

[Bmiller4evr]

I have a list of real property addresses that comes to me from the county. unfortunately the county has an odd way of keeping its records. here is a sample of what a few of them might look like:

001234 Main St

000010 Elm St

098701 Oak St

I need a script that I can run manually or trigger upon import that will separate the address into two fields, the street number without those annoying leading zeros and street name, as well as a field that has the street number and name together, again with no leading zeros.

So visualize the results I need, I need the following 3 fields of results produced:

Street #, Street Name, Address

1234, Main St, 1234 Main St

10, Elm St, 10 Elm St

98701, Oak St, 98701 Oak St

thank you

[Mark Hardee]

I have a list of real property addresses that comes to me from the county. unfortunately the county has an odd way of keeping its records. here is a sample of what a few of them might look like:

001234 Main St

000010 Elm St

098701 Oak St

I need a script that I can run manually or trigger upon import that will separate the address into two fields, the street number without those annoying leading zeros and street name, as well as a field that has the street number and name together, again with no leading zeros.

So visualize the results I need, I need the following 3 fields of results produced:

Street #, Street Name, Address

1234, Main St, 1234 Main St

10, Elm St, 10 Elm St

98701, Oak St, 98701 Oak St

thank you

You will have to scan data to see if it follows a pattern. Read up on Middle and Position functions... Or just get AHunter3 to do it for you! He is a zen master!

[Bmiller4evr]

the street numbers are always 6 digits long, but as you know, real address numbers can vary in length. so the street name i guess would always start at the 8th character. but i am still not sure how to write this script. I am new to scripting, and very tired of exporting to excel and re-importing back.

[comment]

You could do it this way: import the address into the StreetName field, then - while the found set still contains the newly imported record only - follow with:

Replace Field Contents [ Street# ; GetAsNumber ( Left ( StreetName ; 6 ) ) ]

Replace Field Contents [ StreetName ; Right ( StreetName ; Length ( StreetName ) - 7 ) ]

The full Address field should be a calculation field. Note that this assumes the street number contains only digits - otherwise the calculation needs to be more complex.

[Bmiller4evr]

what calculation would be used to put the full address back together?

[comment]

Street# & " " & StreetName

[Bmiller4evr]

Thank you, you have been most helpful :)