We have reset all users FileMaker related profile fields. Please take the opportunity to update your information,  this will provide background to members whom read your posts. Click here.

Jump to content
CKonash

Separating an address field into two fields.

Recommended Posts

CKonash    0

Hello, 

I started a database for a volunteer fire department back in 2012 and they have been storing preplan data for individual addresses since they started using a field "Address/Location".  They are now going to align themselves with the format that the Police department is currently using which has separates fields for the address number and the address street.   

I have created two fields "AddressNumber" and "AddressStreet" and thing a replace field contents step is what I want with a calculation to pull the relevant data into the new field.  I'm having trouble with the calculations to pull just the address number into the first field and just the address street into the second field.   There is no common separator like a ( - ) or anything that separates the address number and the street name.   The only thing is a "Space" 

Anyone have a solution to do this.  They have almost 10,000 address records to doing it manually isn't my favorite option.  

 

Thank you in advance for your time.  

Chris

Share this post


Link to post
Share on other sites
comment    1,357

Could you post a representative example of the "old" contents? If the format is not consistent, post several examples representing the most common arrangements.

 

35 minutes ago, CKonash said:

using a field "Address/Location".

Hopefully, you do not have a field whose name contains a forward slash?

Share this post


Link to post
Share on other sites
CKonash    0

Hello,

Thank you very much for the fast reply.

I was very new to Filemaker when I made the original field names.  I have since renamed the address field to "AddressOld" The table is "PrePlan"

1000 Wyckoff Avenue

130 Storms Drive

131 Fardale Avenue

15 Whitney Road

274 Campgaw Road

6000 Rio Vista Drive

1 GREAT HALL ROAD

We have 5 records that have a ( - ) in the address, like this.  I can do these manually if needed.

1-21 FRANKLIN TURNPIKE

 

Thank you!. 

Chris

Share this post


Link to post
Share on other sites
comment    1,357

In all the given examples, you can populate AddressNumber as =

LeftWords ( AddressOld ; 1 )

and AddressStreet as =

RightWords ( AddressOld ; WordCount ( AddressOld ) - 1 )

 

  • Like 1

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now


×

Important Information

By using this site, you agree to our Guidelines.