6 Replies Latest reply on Oct 13, 2014 1:45 AM by robhepworth

    How to select certain text from a single line of text (FM Pro 13)

    robhepworth

      Hello everybody,

       

      I would really appreciate some help with a problem I am struggling with.

       

      In this exmample I have thousands of records with a field I have named "Product description". The typical product description field looks like these:

       

      HP 9000/9040/9050/9500 2000 Sheet Tray Casette Only (RG5-6212) or (C8531-69019)
      HP 9000MFP Flatbed Intermediate PCB (RH5-3073)
      HP 9040/9050/9500 Copy Connect Board (Q6006-60001)
      HP 9040/9050/9500/9500MFP EIO Copy Processor (Q6005-67901)
      HP 9040MFP / M9050MFP Cover, Flatbed, Right, ADF (RB2-8063)

       

      I am trying to strip out the part number which is contained at the end of each field between the "(" and ")" . As you can see from the first record, sometimes there is two part numbers but it is the last one for each record that I am interested in.

       

      The problem is, is that the part numbers and all different lengths and the content of the field in question is also different lengths.

       

      I have managed to solve this in excel but I ideally need to do it in filemaker. In Excel, I created an additional 5 fields the fifth being the result

       

      The 1st field, calculates the furthest along "(" in terms of characters

      =FIND("^",SUBSTITUTE(A2,"(","^",LEN(A2)-LEN(SUBSTITUTE(A2,"(",""))))

       

      2nd field was:

      =MID(A2,B2,50)

       

      3rd field was:

      =FIND(")",C2,1)

       

      4th field was:

      =LEFT(C2,D2)

       

      5th (result field) was

      =MID(E2,2,D2-2)

       

       

       

      I tried to replicate it in Filemaker but I can't do it. Does anyone know the best way to do it?


      Kindest regards


      Rob