Jump to content
CKonash

Separating an address field into two fields.

Recommended Posts

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

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

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

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

Comment, 

Thank you so much, that worked perfectly!!

I really appreciate it.  

Chris.  

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Who Viewed the Topic

    1 member has viewed this topic:
    guit4eva 
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.