Changing 1 field into two fields

[Neville]

I am trying to export our data to another system (not FM) in order to get our listings up on another web site.

In my FM system, we have "property address" which contains data such as "10 Smith Street" or "Lot 1/10 jones street".

In the other system (to be exported as comma delimited) we need "property address" split into "Street Number" field and "Street name" field.

Seems very simple request but have checked the forums and can't work out how to do it.

Help appreciated.

[kenneth2k1]

hey!

I was wondering: are you exporting to MS Excel? If so, you could (in Excel) select the row you want to split, go to Data/ Text to Columns/ select Fixed Width, set where you want the break and click finish.

Perhaps the pros in this forum could help you otherwise. If nothing else, you can export to excel, make these changes, and then import from excel into your program.

Other than that, I'm afraid I am not much help.

Ken

[danjacoby]

In most cases, you can use the LeftWords and MiddleWords commands to parse out the first "word" of the address, but in the 2nd example you gave, you probably want to parse out the first two "words".

My first suggestion (I may have others) is to create a number field, go through the records to choose how many of the left-side "words" you want to parse out (you could run a script to auto-enter a "1" in this field in every record, since that'll usually be the case), then use the LeftWords & MiddleWords commands, choosing the contents of the number field to determine number of words you parse out.

[Rigsby]

This is a tricky one. Dividing one field into 2 is actually very easy, but your problem is deciding where to divide. I don’t know how many records your file has, but if it’s just a few hundred, or even up to about 2000, I would consider sitting down on a wet weekend and doing this by hand – your data structure will thank you for it. Otherwise, read on….

You need to divide your addresses into categories. In your example, you give 2 different categories:

"10 Smith Street" or "Lot 1/10 jones street"

So here we have a) just streets, and Lots. Lets take ‘Lot’ as our example:

The first thing you need here is a new field for the house or lot number, (I presume you also want the word lot in here too?)

Perform a find for the word Lot

Now you need a script: This script will set your new field (lets call the field AD1 (AD2 is your field with the full address) to the word Lot and Lot number, also including the ‘/’ as in your example, then remove this from your original field AD2:

----------------------------------------

Set Field AD1

Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( AD2, "Lot", ""),"1", ""),"2", ""),"3", ""),"4", ""),"5", ""),"6", ""),"7", ""),"8", ""),"9", ""),"0", ""),"/", "")

--------------

Set Field AD1

Trim(AD1)

--------------

Set Field AD2

Substitute( AD2; AD1; "")

--------------

Ok, now if you test this you will see that it works on one record, then you can put in a loop and go to next record to run through the whole found count.

When you’ve got all the Lots sorted out, you can use the same script to get the house numbers, just remove one Substitute and the ‘Lot’ to get the house numbers into the new field.

Hope this helps you on the right track!

Rigsby

[Rigsby]

PS: Sorry, those should have been commas in the last part of the script:

Set Field AD2

Substitute( AD2, AD1, "")

[Neville]

Thanks again Rigsby

I took your first advice and sat down and did manually (~800 records)(was't wet day - lovely sunny day in our paradise!)

As expected this was best as the original data was a bit messy and inconsistent eg

Lot 1/ 21 Jones st

L1 21 Jones

21 Jones/L 1

L1&L2/21-23 Jones st

etc etc

Took about three hours so wasn't to bad

Thanks

[Rigsby]

Always the best way if it can be done!

-)

Rigsby