3 Replies Latest reply on Mar 4, 2013 2:12 PM by philmodjunk

    Functions from Excel

    cschul

      Title

      Functions from Excel

      Post

            

           This is the formula I'm using in Excel.

            

           =IF(SEARCH("Cat. #",AN2),LEFT(AN2,FIND("(Cat. #",AN2)-1)&""&MID(AN2,FIND("(Cat. #",AN2)+17,999),AN2)

           Is there anything similar to Search or the Find functions in excel in FileMaker Pro?

           I'm trying to figure out how to find a certain line of text in a field and remove it. The text has wildcards in it.

           I have found functions that may be able to help me, however it looks like they are for Advance only.

        • 1. Re: Functions from Excel
          philmodjunk

               Can you provide an example of what you are trying to do. and what role is played by the "wildcards".

               FileMaker has quite a few text functions, including the position and pattern count functions that can search the text in a field, but the role of those wildcards may be a major issue here that makes it necessary to use a Custom Function (requires Filemaker Advanced to install the function) or a script to do the necessary substitution.

          • 2. Re: Functions from Excel
            cschul

                 I would have a line of text in a description field that says "(Cat. #AB123456)". The text will vary after the "#". It can be located anywhere within the description and would even show up mutliple times. I have a better understanding of Excel and Access than I do FileMaker Pro. So I built the formula in Excel first to see if I could then work it into FileMaker. I wouldn't mind just working out of Excel and just importing it into FileMaker, however, another person has to run the script that doesn't have Excel experience.

            • 3. Re: Functions from Excel
              philmodjunk

                   Ok, but what do you want to do with that text once each instance is located within that field?

                   You may have a very good understanding of what your excel functions do, but I am not familiar with the ones you are using here so I need more explanation before I can spell out a way to do this in FileMaker.

                   You may want to take a look at the Find/Replace function that can be performed from the Edit menu or in a script as a way to get the results that you need here.

                   

                        The text will vary after the "#"

                   Will the number of characters after the # be the same or will that also vary?