mac How to parse 1 field into 6 separate fields

[BAW]

I have been given responsibility for making sense out of between 750-1000 records that contain the legal description of land parcels containing oil & gas mineral leases. The listings currently all have a single field containing that information. I need to parse that single field into 6 new fields. 

 

The original single field I am starting from looks something like this:

 

Section 36-3N-10W or Section 1-13S-2E or Section 23-17N-9W. These describe the "Section, Township & Range" of each piece of land. The separators between Section-Township-Range are always plain hyphens with no spaces. The first position is always the Section Number. The middle position is always the Township Number & Township Direction. The end position is always the Range Number & Range Direction. The Section, Township & Range always contain either a 1 or 2 digit number. 

 

Anything to the left of Section is considered Sub-Section information, e.g., N/2 NE/4 S/2 Section 1-9N-19W. (Sometimes there is no information to the Left of "Section".)

 

I want to be able to write one calculation that will break apart the existing data in the single field and automatically paste the pieces into the following 6 fields:   

 

Sub-Section:  All of the information to the Left of "Section" if it occurs. 

 

Section:  Just the 1 or 2 digit Section number. Ignore the word "Section".

 

Township Number:  Just the 1 or 2 digit Township number. Ignore the hyphen. 

 

Township Direction:  Just the "N" or "S" that follows the Township number.

 

Range Number:  Just the 1 or 2 digit Range number. Ignore the hyphen. 

 

Range Direction:  Just the "E" or "W" that follows the Range number. 

 

 

Is this possible?

 

Thanks for any help you can give me in understanding how to do this!

 

Bruce Williamson

[LaRetta]

Hi Bruce, welcome to FMForums!

 

Is this a one-time transition or an on-going need?  Well, either way, the attached file should do the job.  If any needs adjusting let me know. :-)

Oh, I use a script to break the pieces apart into separate fields and it runs through the record set.  The calculations exist in the script.

Parcels.fmp12.zip

[BAW]

LaRetta,

 

This problem has been on my mind for many months. The script you created is EXACTLY what I was looking for! I am overwhelmed! Thank you, thank you, thank you!

 

This process mostly involves a one-time processing of a batch of existing legal data that I imported into FM Pro after some pretty gnarly OCR conversion of an old spreadsheet. Any brand-new records created will have the legal info entered directly into those 6 new fields. Then the basic parcel info will be assembled with concatenation calc fields into any number of different legal formats as needed.

 

Thank you again for your thoughtfulness, kindness & creativity,

 

Bruce Williamson

 

[BAW]

LaRetta,

 

Good morning! You asked if anything needed adjustment. :-) This morning I remembered another set of files I still need to process and tried out your script on them and it partly worked. The main difference between the examples I used last evening and this other batch is that most of these 700+ land parcels are written without the word "Section". The SubSection part is pretty much the same, but suffers from inconsistent formatting, e.g. sometimes a bunch of extra spaces have been added after the SubSection before the SectionNumber, and occasionally there are parentheses bracketing the SubSection info that need to be left out if possible. 

 

Here are some examples of how the existing script works on a few of these new parcel listings:

 

W/2 & SE/4 5-12S-17W does not return the SubSection.

 

NW/4 23-16S-12W does not return the SubSection. 

 

N/2 NW/4  24-20S-11W  does not return the SubSection or RangeDirection. (This one has an extra space after 11W.)

 

(S/2 S/2 N/2)  33-21S-3W does not return the SubSection. 

 

  12-16S-14W does not return the SubSection or RangeDirection. 

 

Is this something that's easy to adjust so that the one beautiful script will handle both situations, with/without the word "Section" and with/without some of the formatting inconsistencies in the SubSection info?

 

Also, since I am learning how these kinds of complex scripts work, is there a way to make a slightly adapted copy of this script that would work on a single record instead of all the records?

 

Thanks again for your help. I am still stunned at seeing all my entries from last evening get analyzed and put into the new fields in just a few seconds! From your experience is it safe to say that Excel is not capable of this kind of flexibility and power?

 

Bruce Williamson

[Helpful Harry]

Excel has pretty much all the same functions, so can easily parse text in a similar way to FileMaker, although without using VisualBasic programming it may require extra columns to do sub-parts of calculations that can be done in FileMaker using a Loop command.

[LaRetta]

 

Is this something that's easy to adjust so that the one beautiful script will handle both situations, with/without the word "Section" and with/without some of the formatting inconsistencies in the SubSection info?

 

Since the only true consistency was based upon the ending ##-###-###, I modified the perspective to backtrack from the end ( please see attached Parcels2 ).

 

 

 

Also, since I am learning how these kinds of complex scripts work, is there a way to make a slightly adapted copy of this script that would work on a single record instead of all the records?

 

 

Simply duplicate this script, delete the first three lines ( Show All, Go To First, Loop ) and delete the last two ( Go To Next and End Loop ).

 

 

From your experience is it safe to say that Excel is not capable of this kind of flexibility and power?

 

They are different tools for different purposes.  However, from my experience, there are only a handful of things that Excel can do that FileMaker cannot but there are hundreds of things that FileMaker can do that Excel cannot. :-)

Parcels2.fmp12.zip