Jump to content
Sign in to follow this  
fecan

Need help separating an address field into 3 fields

Recommended Posts

Hi, does someone know how i would separate an address field that is currently entered as

"1234/100 Smith Street" =(owner_address) into "1234" as (Owner_unit), "100" (owner_street_number) "smith" as (owner_street)

I am a newbie with basic knowledge.

I am guessing i need to create 3 new fields with the formulas to automatically pull them from the one field.

Thank you in advance, any help would be much appreciated.

Share this post


Link to post
Share on other sites

Hi fecan, and Welcome to the Forum.

Having a single field that contains a string of data, such as full names, full address, is a very common error made by new users. It is always best to break these fields into the as many parts as you can, do this, will it makes things a lot easier for your future needs.

Anyway, it is unclear to me if this data already resides in a field in your file, or if it is text to be imported.

It is always better to include more information when posting than you might think is necessary, so that we get the best picture of what it is you are trying to accomplish. Always use actual data when possible, and actual fields, relationships, layouts, etc Names when asking your questions. You will be surprised how much better the answers will be, and in most cases the calculations, etc. with be tailored to your own solution file.

If this is a text import, include a couple of lines of the document, so that we can see if there are any separators that can help with the parsing of your text.

If this is data that is already in a field, show us the data as it appears in the field, i.e., do not use separators such as quotes, commas, periods, etc. if they are not actually in the field.

HTH

Lee

Share this post


Link to post
Share on other sites

Hi Lee,

Thank you very much for your reply. It was so fast!

The database was purchased with the fields already set up with the address in one field.

The data has already been imported (there are about 10,000 records)

The data in this particular field is laid out exactly as 1234/100 Smith street.

I need 3 new fields to work out and display:

*unit number is to the left of the forward slash

*street number is to the right of the forward slash

*street name is to the right of the first space.

*ignore any other spaces. (or make it a forth field being street type- not necessary though)

Apologies if I am still unclear, I am not sure how else to explain it but will keep at it if I am making sense at all.

Thanks again,

Duncan.

Share this post


Link to post
Share on other sites

Try:

unit number =

LeftWords ( Substitute ( LeftWords ( address ; 1 ) ; "/" ; " " ) ; 1 )

street number =

RightWords ( Substitute ( LeftWords ( address ; 1 ) ; "/" ; " " ) ; 1 )

street name =

RightWords ( address ; WordCount ( address ) - 1 )

Note that this assumes that the unit and street numbers are numeric (at least where they border, i.e. a pattern of *#/#* ). If you have records like "123a/5678 ..." then you will need another approach.

Share this post


Link to post
Share on other sites

Thank you!

I will give it a go and let you know how I went.

Regards,

Duncan.

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
Sign in to follow this  

×

Important Information

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