Separating an address field into two fields.

[CKonash]

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

[comment]

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?

[CKonash]

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

[comment]

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

LeftWords ( AddressOld ; 1 )

and AddressStreet as =

RightWords ( AddressOld ; WordCount ( AddressOld ) - 1 )

 

[CKonash]

Comment, 

Thank you so much, that worked perfectly!!

I really appreciate it.  

Chris.