Break Address Into Three Parts (if possible)

[Rich Hartnett]

Is it possible to create an expression that would break up the following string into three fields:

1234 Main Street Apt. 123

Into:

1234 / Main Street / Apt. 123

In my database, almost all of the street addresses have 4 numbers in them, and when an apartment number is entered, it almost always begins with "Apt."
I can deal with the few that are entered oddly.

I can also deal with multiple expressions if need be.

Rich

[Al Buchholz]

Re: Break Address Into Three Parts (if possible)

Unzip this table into a test workspace and look at the update operation.

Rich_address.zip

[Rich Hartnett]

Re: Break Address Into Three Parts (if possible)

Thanks for the quick response Al.

I downloaded and installed your table, but it doesn't seem to do anything.

I added a new address and made sure the record was accepted, but the three additional fields did not parse the first field.

I noticed there are no field rules assigned to the three new fields. Are they supposed to by any?

[Al Buchholz]

Re: Break Address Into Three Parts (if possible)

Thanks for the quick response Al.

I downloaded and installed your table, but it doesn't seem to do anything.

I added a new address and made sure the record was accepted, but the three additional fields did not parse the first field.

I noticed there are no field rules assigned to the three new fields. Are they supposed to by any?

Unzip this table into a test workspace and look at the update operation.

Run the operation - splitAddress - from the control panel.

If you open the operation in design mode, you can see the coding to split the address.

[Rich Hartnett]

Re: Break Address Into Three Parts (if possible)

Yeah sorry about that. I had phones ringing and people parading in and out of my office while I was working on the download.

I guess I missed the part about the update operation.

However I tested it and it works like a champ!

Many Thanks as always!!

[Al Buchholz]

Re: Break Address Into Three Parts (if possible)

Yeah sorry about that. I had phones ringing and people parading in and out of my office while I was working on the download.

I guess I missed the part about the update operation.

However I tested it and it works like a champ!

Many Thanks as always!!

That example is not meant to be the finished product, but an example of what can be done.

I'm not sure how you want this to work, but it should give you a jump start.

[Rich Hartnett]

Re: Break Address Into Three Parts (if possible)

Al I'm back again with an additional question .....

As it turns out, most of the addresses in this database do not (after all) have a period in the "Apt. XX" section. As such, the expression is not removing the "Apt XX" whenever it is in the table without the period.

I tried modifying your expression by simply removing the period, but it didn't seem to make the "Apt XX" part parse out.

Here's your expression:
if(word(Address,2,"Apt.")>"","Apt."+word(Address,2,"Apt."),"")

Here's what I tried:
if(word(Address,2,"Apt")>"","Apt"+word(Address,2,"Apt"),"")

Could you possibly advise me on how to modify this so it will work correctly?

Thanks again,
Rich

[Al Buchholz]

Re: Break Address Into Three Parts (if possible)

Al I'm back again with an additional question .....

As it turns out, most of the addresses in this database do not (after all) have a period in the "Apt. XX" section. As such, the expression is not removing the "Apt XX" whenever it is in the table without the period.

I tried modifying your expression by simply removing the period, but it didn't seem to make the "Apt XX" part parse out.

Here's your expression:
if(word(Address,2,"Apt.")>"","Apt."+word(Address,2,"Apt."),"")

Here's what I tried:
if(word(Address,2,"Apt")>"","Apt"+word(Address,2,"Apt"),"")

Could you possibly advise me on how to modify this so it will work correctly?

Thanks again,
Rich

Sure.

Send me an email with some sample addresses that you are using.

I don't want the data posted on the messageboard.

[Al Buchholz]

Re: Break Address Into Three Parts (if possible)

Al I'm back again with an additional question .....

As it turns out, most of the addresses in this database do not (after all) have a period in the "Apt. XX" section. As such, the expression is not removing the "Apt XX" whenever it is in the table without the period.

I tried modifying your expression by simply removing the period, but it didn't seem to make the "Apt XX" part parse out.

Here's your expression:
if(word(Address,2,"Apt.")>"","Apt."+word(Address,2,"Apt."),"")

Here's what I tried:
if(word(Address,2,"Apt")>"","Apt"+word(Address,2,"Apt"),"")

Could you possibly advise me on how to modify this so it will work correctly?

Thanks again,
Rich

You also need to modify the Street expression to remove the period from Apt

Street expression should be:

word(word(Address,2," ",9),1,"Apt")

[Rich Hartnett]

Re: Break Address Into Three Parts (if possible)

Works perfectly!

Thanks again Al!!

[peteconway]

Re: Break Address Into Three Parts (if possible)

Nice support Alan.