3 Replies Latest reply on Jul 30, 2015 4:56 PM by philmodjunk

    Need help removing *text* from field

    JohnV_1

      Title

      Need help removing *text* from field

      Post

       

      Hi,

      I need help removing certain text from a text field. Below is the sample text.

      Basically, in this case, I need all the text removed (including the date) starting at 11/19/2013 and ending at the last phone number of, in this case, Jeff Kate. Some records may only have two contacts- this one has three. And it's possible that the Work # may be blank, but if it is, the text "Work:" will still be there.

      There may be text in this field that doesn't have this format (date time stamp, name, cell, home, work). That stuff I need to keep.

      Thanks in advance for any help!

      11/19/2013 4:46:31 AM
      Bob Smith
      Cell: (555) 555-1212
      Home: (555) 555-1212
      Work: (555) 555-1212
      
      11/19/2013 4:46:30 AM
      Laura  Pluto
      Cell: (555) 555-1212
      Home: (555) 555-1212
      Work: 
      
      11/19/2013 4:46:30 AM
      Jeff Kate
      Cell: (555) 555-1212
      Home: 
      Work: (555) 555-1212
      
      89/11Emergency Contacts: Bill Smith (aunt) (555) 555-1212 and Tim and John Henry (uncle) (555) 555-1212

        • 1. Re: Need help removing *text* from field
          philmodjunk

          Do you mean that in the  example shown, the only data you wish to retain is the very last line of text? "89/11 Emergency...."

          • 2. Re: Need help removing *text* from field
            JohnV_1

            Hi Phil, thanks for your response. Yes to your question above. Any idea where one could read up and learn more about manipulating text?

            Comment over on fmforums.com provided a script that worked beautifully:

            If I am guessing correctly and you want the part of the (entire) text after the last line that starts with "Work:", then you could probably use =

            Let ( [
            len = Length ( text ) ;
            marker = Position ( text ; "¶Work:" ; len ; -1 ) ; 
            end =  Position ( text ;  ; marker ; 3 )
            ] ;
            Right ( text ; len - end )
            )

            Note that this assumes there's an empty line separating the phones and the rest of the text.

            • 3. Re: Need help removing *text* from field
              philmodjunk

              What they recommended seems excellent. If the text you want to keep always contains the text "emergency contacts", that might also be used to parse out only the text at the bottom of the field. All such methods rely on some basic consistency to how the data is organized inside your field from record to record.

              All of the functions shown in the above example can be looked up in FileMaker help. So I suggest looking up any function names that are unfamiliar as the first step. From there you are welcome to ask questions about specific details if any part of this calculation still does not make sense. I'm sure the FM Forums folks would also be willing to explain.

              Also, if you have FileMaker Advanced, you can enter the above expression into the Data Viewer as a watch expression. Then you can dissect it by adding/removing different parts of the expression as a way to better understand how each part of the calculation works.