7 Replies Latest reply on May 18, 2011 11:48 AM by LindaWilkes

    inserting a character (comma, paragraph return) into text string

    LindaWilkes

      Title

      inserting a character (comma, paragraph return) into text string

      Post

      I have a text string that I want to add a character like a comma or paragraph return every 4 characters.

      For example I have a string like GE09GE08GE07 and I need to separate this into GE09,GE08,GE07

      Is there a calculation function that can do this?  I've tried left with repeats but it just gives me the same 4 characters over and over, not the next four.

      HELP!

      Linda

        • 1. Re: inserting a character (comma, paragraph return) into text string
          LaRetta_1

          Hi Linda,

          If there is a pattern then you can use a calculation.  You can also use a script to loop through the string in each record.  Ideally, if there are an unknown number of characters in the string and you want to split every fourth character then you should use a recursive custom function.  What version of FM are you using and are you using FM Advanced?

          Does every four characters always start with set text, such as GE in this example? Or does the third and four character always represent a number?

          If we can identify the pattern then it is easy even without custom function.  For instance, if you have a global text field called char and each fourth always begins with a G then a calculation (result is text) could be:

          Substitute ( Trim ( Substitute ( string ;  "G" ; " G"  ) ) ; " " ; char )

          • 2. Re: inserting a character (comma, paragraph return) into text string
            LindaWilkes

            Hi LaRetta

            I'm using Filemaker Pro 11.

            Each of the four character sets starts with two letters and ends with two numbers.  No pattern with respect to the letters or numbers however.

            So the two letters could be GE or SC or whatever.  The numbers stand for years so they are 09 or 07 or 11, etc.

            I'm just trying to split up the string into 4 character units so I can use the data.

            So GE09 stands for General Election in 2009, PE07 is Presidential Election 2007.

            Any help you can give would be appreciated.

            Thanks,

            Linda

            • 3. Re: inserting a character (comma, paragraph return) into text string
              LaRetta_1

              Hi Linda,

              It would be easy with custom function but you don't have Advanced.  I could also give you a recursive regular calculation but that would be a hack so I won't do that; instead I will give you script.  However, is this a one-time thing or an ongoing process? In truth, if you are going to go to the trouble of adding a character between each 'entity' in field called TEXT then why not go ahead and place them in related fields as they should be?

              Anyway, here is simple script to add a character between.  If you wish to split them truly apart, let me know and we can walk you through that as well.

              I suggest that you create a 'final' field to hold the information - let's call it TEXT2. Also create a text field (set it to global) and call it gChar (meaning this is where you will enter the character that you wish to use to insert between every 4th character).  We will leave your original field in tact for protection and write to TEXT2 but as safeguard, back up your file before running this:

              Overview: Script will loop through records and loop through TEXT within each record. The looping will take place in variables because it is faster than setting the field each time.

              If [ IsEmpty ( table::gChar ) ]
              Show Custom Dialog [ Message: "You didn't enter a separator character into gChar. Try again."; Buttons: “OK” ]
              Halt Script
              End If
              #
              Show All Records
              Go to Record/Request/Page [ First ]
              Loop
              Set Variable [ $string2; Value:"" ]
              Set Variable [ $string; Value:table::TEXT ]
              Loop
              Exit Loop If [ IsEmpty ( $string ) ]
              Set Variable [ $string2; Value:$string2 & Left ( $string ; 4 ) & table::gChar ]
              Set Variable [ $string; Value:Right ( $string ; Length ( $string ) - 4 ) ]
              End Loop
              Set Field [ table::TEXT2; Left ( $string2 ; Length ( $string2 ) - 1 ) ]
              Go to Record/Request/Page [ Next; Exit after last ]
              End Loop

              • 4. Re: inserting a character (comma, paragraph return) into text string
                LindaWilkes

                Hi LaRetta,

                This is a one time thing.  The data was sent to me as is. 

                I've never used a script before with filemaker.  I assume I copy and paste the script somewhere in the script manager.  If I do that by creating a new script and putting perfom script in and paste in your script in specify, I get a message that it is looking for a table.

                Not sure if that's what I do.

                I did change the name of the field to text and added one called text2.

                Ideally if the text string could be split apart that would be great.

                Thanks!!

                Linda

                • 5. Re: inserting a character (comma, paragraph return) into text string
                  LaRetta_1

                  Hi Linda, please remove your phone number.  Sometimes spy bots grab information you don't want the world to have.

                  And  no, you don't need to pay me but I would need a bit more information.   You will end up with several 'like' fields which means that they should  be in a related table.  We can handle that as well but I am not  available for next few hours.  If you send me a private message here  with an example of how this current table (and these values would  relate, i.e. I need to know a key value between them) then I can create  the script for you.

                  And no, you can't copy and paste into your  script maker.  You must manually recreate it.  Where I have table:: you  must change that to point to your real table name which I never knew so I  inserted 'table' to indicte your table name.

                  I need to know the table name and unique ID for this table and I can probably finish this for you tonight.

                  • 6. Re: inserting a character (comma, paragraph return) into text string
                    LindaWilkes

                    I don't have a table, just a field with the data string, ie, GE09GE08GE07.  I'd like to be able to search for say all the records with GE09.

                    What would be ideal is if you could do a calculation with left that inserts a comma or some character every four characters but keeps going to the left.

                    This may be an impossible task.

                    The data came in originally from an excel spread sheet for what that's worth

                    Thanks,

                    Linda

                    • 7. Re: inserting a character (comma, paragraph return) into text string
                      LindaWilkes

                      Hi LaRetta,

                      I received the file and it worked.

                      Thank you soooooooo much!

                      I'm in your debt.

                      Linda