Jump to content
Sign in to follow this  

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.



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,


Share this post

Link to post
Share on other sites


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.



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.

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.

Sign in to follow this  

  • Create New...

Important Information

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