6 Replies Latest reply on Jan 7, 2013 9:18 AM by Annette

    Splitting text in one field to multiple fields



      Splitting text in one field to multiple fields



           I am trying to import records into a database I currently have from an excel file.  First what I did was just create a basic filemaker database and import the records into it.  What the user did was put the address in one field seperated by commas like this; 1, This Street, This Estate, Town, County

           I have that imported into a field called Full Address.  I have fields in the database called Address 1, Address 2, Town, County. 

           I'm not sure how to begin pulling the information into the seperate fields.  I know its a calculation but where do I put the calculations and what is it?  Also, is there a way to differentiate those that may only have the number and street and not the estate like the example above?  I think it would be something like anything before and after first comma go in Address 1, to the right of last comma in county, left of last comma in town and anything in middle of those in address 2?  Or maybe I just don't know what the hell I'm doing and should shut up ha ha. 

           Any help would be greatly appreciated. 


        • 1. Re: Splitting text in one field to multiple fields

               In computer terms, Consistency Is Next To Godliness.  What you have here is not a problem, if the addresses were all consistent in the placing of their components and commas.

               Your first decision, I think, is whether to try and produce consistency before you try and parse them, or parse them and then try and sort the anomalies.

               You can use the Position function to determine where each comma is (you can find the first instance, the second instance, etc).

               You can use the Middle, Left, or Right functions to extract characters to or from the comma position, say.

               You can use the Trim function to strip off any leading or traling spaces on the result.

               Combining those functions will extract the parts of that full address, but as you say if the third comma-to-comma section sometimes contains the Estate and sometimes the Town you will have to amend those manually.  (Unless, for example, you have 'second level consistency', and whenever something is missing it is always the Estate.)

               Your next decision is whether to leave that Full Address field as-is, and create 5 calculations (one for each component) from it.  If you are consistent from now on, that will work, but I wouldn't recommend it.  It is always easier to enter smaller bits of data and combine them, than split them out later.

               I would recommend that you create 5 more calculation fields (one for each component) and then perfect each calculation.  Then copy that proven calculation, change the field to be a text field, then use the Replace Field Contents menu option to replace (via calculation - that copied calculation) into the field.  Repeat for the other components.  Then you have full access to edit and fix any anomolies, and from now on you should enter the components directly into those component fields.  When you have all addresses purged and corrected, you can change the Full Address field to be a calculation field, and then build it up from the separated components.

               Make a backup first!

          • 2. Re: Splitting text in one field to multiple fields

                 I understand what you are saying...sort of..ha......but my problem is as I'm still new to calculations and scripts I don't really know the correct way to write them out. 

                 So if Full Address field = 1, My Street, Town, County   would I then put a calculation in each of the fields below? 





                 And my other problem is writing out the calculations.  Do I use position and left/middle/right in the same calculation?  How do I combine them, etc.  I'm really sorry but these things still really confuse me. 

            • 3. Re: Splitting text in one field to multiple fields

                   Here's an alternative to using the position function:

                   Substitute ( AddressField ; "," ; ¶ )

                   this converts your comma separated list into a return separated list and you can use the GetValue function to extract specific values from this list.

                   Set Variable [ $AddressList ; value: Substitute ( YourTable::AddressField ; "," ; ¶ ) ]
                   SetField [YourTable::Address1 ; GetValue ( $AddressList ; 1 ) ]

                   For example will extract everything to the left of the first comma and put it in Address1.

                   Learned this trick from Raybaudi.

                   But as Sorbsbuster indicated, a lack of consistancy could make your efforts to parse out the data from this one field less than perfect. THe second value in your list could be data for Address2 or it could be data for Town.

              • 4. Re: Splitting text in one field to multiple fields

                     THanks very much Phil that worked the best for me!  I did have to fix some of them but it was rare. 

                     Is there an alteration to that so that it removes any spaces between the lines? At the moment when I use the substitue function to get them in lines from the second line onwards there is a space at the beginning of the line.

                • 5. Re: Splitting text in one field to multiple fields

                       You might try using the Trim function:

                       SetField [YourTable::Address1 ; Trim ( GetValue ( $AddressList ; 1 ) ) ]

                  • 6. Re: Splitting text in one field to multiple fields

                         Thanks Phil!  I was trying it in the Substitue part and it wasn't working