2 Replies Latest reply on May 1, 2009 4:01 PM by comment_1

    Need a formula in my calc field that will look for more than twenty different words

    RigneyJim

      Title

      Need a formula in my calc field that will look for more than twenty different words

      Post

      Hello,

       

       I was able to write this:   Substitute ( Text; "SUN" ;(TextStyleAdd ("SUN" ;Bold )))

       

      This will look in my Text field and search for the word "SUN" and turn the word "SUN" bold. 

       

      I have a data base with over 200 files (Speaches) containing a text field (Text) which contains a speach by someone.

      I want a formula that will look at the Text field in each Speach file, search for certain words, and change each word bold.

       

      I have a list of 40 or 50 key words I am searching for in the Text of the Speach files, and want each of the words, if it is found, to turn bold in the text field.  

       

      Some of the words I want to turn bold are: "Vertical", "horizontal," "center," "spiral, "cosmic spring", etc.

       

      How can I do it?

       

      Thank you.

        • 1. Re: Need a formula in my calc field that will look for more than twenty different words
          philmodjunk
            

          "I have a data base with over 200 files (Speeches) containing a text field (Text) which contains a speech by someone."

          Perhaps you mean 200 records in a single file?

           

          I think it might be easier to write a script that performs the style change one selected word at a time.

           

          If you really want to do this in a single calculation, you could nest your use of the substitute function like this:

           

          Substitute ( Substitute ( Text; "SUN" ; TextStyleAdd ("SUN" ;Bold )) ; "Vertical" ; textstyleAdd("VERTICAL" ; Bold ))

           

          Note that your expression also changes all instances of the word to all caps as well as bold.

           

          The resulting expression needed for 40 or 50 key words would be truly horrendous in complexity.

           

          Instead, I'd put all the key words in either a single text field separated by carriage returns or a separate records in a table.

           

          Then my script could be set up in a loop to perform a replace field contents with a single Substitute calculation but for a different word each time.

           

           

          • 2. Re: Need a formula in my calc field that will look for more than twenty different words
            comment_1
              

            If your keywords are static, you could do:

             

            Substitute ( Text;

            [ "Vertical" ; TextStyleAdd ( "Vertical" ; Bold ) ] ;

            [ "horizontal" ; TextStyleAdd ( "horizontal" ; Bold ) ] ;

            [ "center" ; TextStyleAdd ( "center" ; Bold ) ] ;

            ...

            )

             

            If not, you will need a custom recursive function (requires Filemaker Advanced) or a script to achieve this - see example.

             

            --- 

            Note: the Substitute() function is case-sensitive - in the above example, "vertical" and "Horizontal" would NOT be highlighted.