Need help separating an address field into 3 fields

[fecan]

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.

[Lee Smith]

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

[fecan]

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.

[comment]

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.

[fecan]

Thank you!

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

Regards,

Duncan.